如何使用纯SQL实现同城市下CAT与DOG对应行的ORDER_INDEX值互换

假设你使用的业务表名为animal_order，以下方案兼容MySQL、PostgreSQL、Oracle等绝大多数主流关系型数据库，执行时不会影响BEE等非CAT/DOG类型的记录。

方案1：自关联更新（推荐，执行效率高） #

通过同表关联匹配每个城市下的CAT和DOG记录，直接交换二者的排序值：

UPDATE animal_order a
JOIN animal_order b 
  ON a.CITY_ID = b.CITY_ID 
  AND a.TYPE = 'CAT' 
  AND b.TYPE = 'DOG'
SET 
  a.ORDER_INDEX = b.ORDER_INDEX,
  b.ORDER_INDEX = a.ORDER_INDEX;

方案2：CASE表达式写法（兼容不支持连表更新的数据库） #

如果你的数据库不支持UPDATE语句中关联表，可以用子查询先捞取对应值再赋值：

UPDATE animal_order t
SET ORDER_INDEX = (
  CASE TYPE
    WHEN 'CAT' THEN (SELECT ORDER_INDEX FROM animal_order WHERE CITY_ID = t.CITY_ID AND TYPE = 'DOG')
    WHEN 'DOG' THEN (SELECT ORDER_INDEX FROM animal_order WHERE CITY_ID = t.CITY_ID AND TYPE = 'CAT')
    ELSE ORDER_INDEX
  END
)
WHERE TYPE IN ('CAT', 'DOG');

执行后结果验证 #

两种方案执行后查询表数据，输出结果符合需求：

可选优化：如果存在部分城市只有CAT/只有DOG的场景，可以在WHERE条件中补充过滤，仅处理同时存在两种类型的城市，避免空值覆盖原有数据。

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