我只是有一个sql疑问,实际上我需要对每种颜色值多次使用更新查询...每次检查每种颜色的条件都变得越来越困难。
在批量“更新”下面是否有任何一条sql语句可以执行?请告诉我..
update `myTable` set `COLOR`='white' WHERE `CATEGORY` = 'Dress' and `NAME` like '%white%'
update `myTable` set `COLOR`='red' WHERE `CATEGORY` = 'Dress' and `NAME` like '%red%'
update `myTable` set `COLOR`='blue' WHERE `CATEGORY` = 'Dress' and `NAME` like '%blue%'
update `myTable` set `COLOR`='pink' WHERE `CATEGORY` = 'Dress' and `NAME` like '%pink%'
最佳答案
您可以使用CASE
Update myTable
SET COLOR = CASE
WHEN CATEGORY = 'white' AND NAME like '%white%' THEN 'white'
WHEN CATEGORY = 'red' AND NAME like '%red%' THEN 'red'
WHEN CATEGORY = 'blue' AND NAME like '%blue%' THEN 'blue'
WHEN CATEGORY = 'pink' AND NAME like '%pink%' THEN 'pink'
ELSE value
END
WHERE CATEGORY IN ('white','red','blue','pink')