mysql - 使用CASE WHEN报错?

Question

使用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

Answer 1

不能在一个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};

Answer 2

你的 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 语句时，你的应用逻辑能够处理可能的更新行数（例如，通过检查受影响的行数）。