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又要更新速度还是很慢
有好的方法么 谢谢大家了
用临时表
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
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。。 prd_no ... wh......
END
首先要确认不能一条一条更新，其次不能用 else ，else不仅可能会改动原数据而且效率低下。一般用WHERE 过滤。以mysql为例，也可用replace into或insert into ...on duplicate key update代替。