如何用单条SQL语句更新同表多行，交换DefaultForAxisID避免报错

可行解决方案 #

使用单条UPDATE语句配合CASE表达式实现原子性更新，避免中间状态触发校验报错：

UPDATE ProcedureCodeCategory
SET DefaultForAxisID = CASE 
    WHEN CategoryID = 21 THEN 2
    WHEN CategoryID = 22 THEN NULL
    ELSE DefaultForAxisID
  END
WHERE ProcedureCodeID = 195837
  AND CategoryID IN (21, 22);

方案说明 #

你遇到的报错是表上的校验触发器导致的：该触发器要求同一个ProcedureCodeID下，每个DefaultForAxisID必须有对应的绑定记录，不允许单独清空已存在的默认值导致无归属的AxisID。
上述语句的所有修改在同一个事务内完成，触发器只会校验更新完成后的最终状态：此时DefaultForAxisID=2已经绑定到CategoryID=21的记录，不存在无主的空窗期，自然不会触发报错，同时满足你单次操作完成修改的需求。

如果需要封装为通用存储过程，可将ProcedureCodeID、待交换的两个CategoryID、对应要设置的DefaultForAxisID值设为输入参数，适配更多业务场景。

内容的提问来源于stack exchange，提问作者Jekt