在一次执行中更新多列

Question

UPDATE tblstoreitems SET price='499' WHERE TypeOrModel = 'A130'; 
UPDATE tblstoreitems SET price='599' WHERE TypeOrModel = 'A140'; 
UPDATE tblstoreitems SET price='1899' WHERE TypeOrModel = 'Alpha Style'; 
UPDATE tblstoreitems SET price='1699' WHERE TypeOrModel = 'Amethyst'; 
UPDATE tblstoreitems SET price='899' WHERE TypeOrModel = 'T18'; 
UPDATE tblstoreitems SET price='1499' WHERE TypeOrModel = 'Ace_f100'; 
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='Aura Fusion'; 
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='Axis'; 
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='B100';  
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='B5';  
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='B8'; 
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='Breeze'; 
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='Breeze 2'; 
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='Bubble'; 
UPDATE tblstoreitems SET price='499' WHERE TypeOrModel ='Burst 2.0'; 

Answer 1

使用下面的quwery将解决您的问题。

update tblstoreitems 
set price = 
case 
when TypeOrModel = 'A130' then 499 
when TypeOrModel = 'A140' then 599 
when TypeOrModel = 'Alpha Style' then 1899 
end 

Answer 2

我觉得你与不同TypeOrModel值更新列price，如果你希望它是在一个语句，你可以更新的东西中使用case when作为

update tblstoreitems 
set 
price = 
case 
when TypeOrModel = 'A130' then '499' 
when TypeOrModel = 'A140' then '599' 
....... 
....... 
when TypeOrModel ='Burst 2.0' then '499' 
end 

Answer 3

您可以更换这些更新使用case表达式进行单个更新：

UPDATE tblstoreitems 
SET price = CASE TypeOrModel 
       WHEN 'A130' THEN '499' 
       WHEN 'A140' THEN '599' 
       -- All the others cases, snipped for clarity 
       ELSE price END; 

Answer 4

查询

UPDATE tblstoreitems SET price= 
CASE WHEN TypeOrModel IN 
(
    'A130','Aura Fusion','Axis','B100','B5','B8', 
    'Breeze','Breeze 2','Bubble','Burst 2.0' 
) 
THEN 499 
WHEN TypeOrModel IN ('A140') THEN 599 
WHEN TypeOrModel IN ('Alpha Style') THEN 1899 
WHEN TypeOrModel IN ('Amethyst') THEN 1699 
WHEN TypeOrModel IN ('T18') THEN 899 
WHEN TypeOrModel IN ('Ace_f100') THEN 1499 
ELSE price 
END; 

Fiddle demo