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

Question

UPDATE 表名
SET 列名1 = CASE 列名2
WHEN 'a' THEN 'tt'
WHEN 'b' THEN 'tt'
END
WHERE 列名2 IN ('a','b')
这种可以多条件么 不会写

update tf_ij set rem='111' where itm='11' and prd_no='MJSP-JJ04B0' and wh='I06-002-1';
update tf_ij set rem='222' where itm='12' and prd_no='MJSP-JJ0500' and wh='I06-002-1';
update tf_ij set rem='333' where itm='13' and prd_no='MJSP-JJ05B1' and wh='I06-002-2';
单条sql如上 set和 where的值都是后台传入的不是数据库表里面的

我试了多条那种单条sql一次性提交很慢，

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'
else rem

END
这种的else又要更新速度还是很慢
有好的方法么 谢谢大家了

Answer 1

用临时表

 create table #tempT(item1 varchar(5),prd_no varchar(20),rem varchar(10))
insert into #tempT values('11','MJSP-JJ04B0','111'),('22','MJSP-JJ04B0','222'),('33','MJSP-JJ04B0','333')
--select * from #tempT
update tf_ij set rem=t.rem from tf_ij inner join #tempT t on t.item1=tf_ij.item and tf_ij.prd_no=t.prd_no and tf_ij.wh='I06-002-1'
drop table #tempT