问答博客资讯标签用户活动
logo极客观点logo项目管理logoHarmonyOS
javascript
前端
python
node.js
react
vue.js
php
laravel
go
人工智能
mysql
linux
ios
java
android
css
typescript
spring
程序员
logoONES 研发管理logo思否企业问答logo安谋科技 XPU

使用CASE WHEN报错?

头像
迎难而上
    93
    头像
    42
      3.9k2624

      使用CASE WHEN 做条件的筛选，但是一直报错；

      select 
  * 
from 
  pnassertchuku_middle (
    case when #{identification} =='2' and (select identification = '1' from pnassertchuku_middle where child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId})
    then 
    update 
      pnassertchuku_middle 
    set 
      identification = '2' 
    where 
      child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId}
      else end as identification case when #{identification} =='0' and (select identification = '1' from pnassertchuku_middle where child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId})
      then 
    update 
      pnassertchuku_middle 
    set 
      identification = '0' 
    where 
      child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId}
      else end as identification
  ) 
where 
  child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId}

      报错：

      ### Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( case when '0' =='2' and (select identification = '1' f' at line 1 ### The error may exist in file [D:\mes-master\mes-master\ruoyi-system\target\classes\mapper\system\pnChuKuMapper.xml] ### The error may involve com.ruoyi.system.mapper.PnChuKuMapper.updatePnassertchukuMiddle-Inline ### The error occurred while setting parameters ### SQL: select * from pnassertchuku_middle ( case when ? =='2' and (select identification = '1' from pnassertchuku_middle where child_pnAssertChuKuDetails_id = ?) then update pnassertchuku_middle set identification = '2' where child_pnAssertChuKuDetails_id = ? else end as identification case when ? =='0' and (select identification = '1' from pnassertchuku_middle where child_pnAssertChuKuDetails_id = ?) then update pnassertchuku_middle set identification = '0' where child_pnAssertChuKuDetails_id = ? else end as identification ) where child_pnAssertChuKuDetails_id = ? ### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( case when '0' =='2' and (select identification = '1' f' at line 1 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( case when '0' =='2' and (select identification = '1' f' at line 1
      mysql
      阅读 763
      avatarAI BotBETA

      你的 SQL 查询中存在多个问题，主要是因为 CASE 语句的使用不正确，并且你试图在 SELECT 语句中直接执行 UPDATE 操作，这是不允许的。此外，你的 CASE 语句的语法也有误，包括使用了不恰当的等号比较（在 SQL 中应该使用单个等号 =），以及 CASE 语句的结束方式不正确（缺少 ELSE 部分的返回值，且整个 CASE 表达式应该作为一个字段的表达式而不是整个查询的一部分）。

      针对你的需求，如果你的目的是根据条件更新表中的记录，你应该直接使用 UPDATE 语句，而不是在 SELECT 语句中尝试执行更新。以下是一个可能的修正方案，它使用两个 UPDATE 语句，根据条件更新 identification 字段：

      -- 假设 #{identification} 和 #{childPnAssertChuKuDetailsId} 是通过某种方式（如预处理语句）传入的参数

-- 当 identification 参数为 '2'，并且子记录中的 identification 为 '1' 时，更新为 '2'
UPDATE pnassertchuku_middle
SET identification = '2'
WHERE child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId}
AND identification = '1'
AND #{identification} = '2';

-- 当 identification 参数为 '0'，并且子记录中的 identification 为 '1' 时，更新为 '0'
UPDATE pnassertchuku_middle
SET identification = '0'
WHERE child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId}
AND identification = '1'
AND #{identification} = '0';

      注意：

      1. 我假设 #{identification} 和 #{childPnAssertChuKuDetailsId} 是通过某种预处理机制（如 JDBC 的 PreparedStatement）传递的参数。确保在数据库操作中使用这些参数时，它们已经被正确地替换或绑定。
      2. 我移除了 CASE 语句，因为在这个场景中，UPDATE 语句是更直接和合适的方式来根据条件更新数据。
      3. 请确保在执行这些 UPDATE 语句时，你的应用逻辑能够处理可能的更新行数（例如，通过检查受影响的行数）。
      1 个回答
      得票最新
      头像
      Undest
        2.4k2516

        不能在一个SELECT语句里套UPDATE语句

        UPDATE pnassertchuku_middle
SET identification = CASE
    WHEN #{identification} = '2' AND (SELECT identification = '1' FROM pnassertchuku_middle WHERE child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId}) THEN '2'
    WHEN #{identification} = '0' AND (SELECT identification = '1' FROM pnassertchuku_middle WHERE child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId}) THEN '0'
    ELSE identification -- 保持原值不变
END
WHERE child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId};
        -- 选择更新后的记录
SELECT *
FROM pnassertchuku_middle
WHERE child_pnAssertChuKuDetails_id = #{childPnAssertChuKuDetailsId};
        撰写回答
        你尚未登录，登录后可以
        • 和开发者交流问题的细节
        • 关注并接收问题和回答的更新提醒
        • 参与内容的编辑和改进，让解决方法与时俱进
        推荐问题
        宣传栏