sqlのupdate case when 的玩法
业务场景:
有部分数据需要按条件做判断来update某个表的字段值。这个我是怎么实现的呢?
sql:
UPDATE user
SET order = CASE
WHEN id = '1' THEN '1'
WHEN id = '2' THEN '2'
END;
当然,这个语句还可以扩展一下,例如如下用法:
示例1:
UPDATE graduates
SET income = CASE
WHEN income = 20000 THEN income * 0.5
WHEN income = 15000 THEN income + 500
ELSE income
END;
示例2:
UPDATE customers
SET age = CASE
WHEN age < 30 THEN age + 1
ELSE age
END;
示例3:
UPDATE customers
SET
age = CASE
WHEN city = 'New York' THEN age + 1
ELSE age
END,
country = CASE
WHEN city = 'New York' THEN 'USA'
ELSE country
END;