sql 单表多条件更新一个字段 大概几百条更新sql优化

Question

UPDATE TF_IJ
SET rem = CASE
WHEN itm='1' and prd_no='tt' and wh='123' THEN 'a'
WHEN itm='2' and prd_no='tt1' and wh='123' THEN 'a1'
WHEN itm='3' and prd_no='tt2' and wh='123' THEN 'a2'
WHERE itm in('1','2','3') and prd_no in('tt','tt1','tt2') and wh in('123')
END

[Err] 42000 - [SQL Server]关键字 'WHERE' 附近有语法错误。

这个应该怎么改呢 或者有更好的sql 谢谢大家了

Answer 1

//上面语法很多处错误，既然有多个判断条件，肯定要多次对比，硬性条件，上面写法明显是错的，试下这种
UPDATE TF_IJ
SET rem = 
CASE WHEN itm='1' and prd_no='tt' and wh='123' THEN 'a' else
CASE WHEN itm='2' and prd_no='tt1' and wh='123' THEN 'a1' else
CASE WHEN itm='3' and prd_no='tt2' and wh='123' THEN 'a2' END  END END 
WHERE itm in('1','2','3') and prd_no in('tt','tt1','tt2') and wh in('123')