如何在同一更新语句中包含多列的多个case语句

Question

我有一个表：

CREATE TABLE UPDATE_LEVEL1_LEVEL2 
(
installment_description varchar(100)
key_id int
ITEM_CATEGORY varchar(100)
ITEM_SUB_CATEGORY varchar(100)
LEVEL1 varchar(100)
LEVEL2 varchar(100)
DEFAULT_VALUE varchar(100) 
)

下面的查询会根据表中已映射的记录自动更新LEVEL1和LEVEL2级。

UPDATE UPDATE_LEVEL1_LEVEL2 ebx
SET ebx.LEVEL1 = config.LEVEL1,
ebx.LEVEL2 = CASE 
WHEN config.ITEM_CATEGORY = 'LOOTCRATE' AND config.=SUB_CATEGORY = 'ND' AND ebx.ITEM_DESCRIPTION LIKE '%CLASSIC%' 
THEN 'PLAYGROUND'
WHEN config.ITEM_CATEGORY = 'LOOTCRATE' AND config.=SUB_CATEGORY = 'ND' AND ebx.ITEM_DESCRIPTION like '%GOLD%' 
THEN 'IGLOO'
ELSE config.CATEGORY_LEVEL2
END
FROM
(SELECT 
INSTALLMENT_DESCRIPTION, ITEM_CATEGORY, ITEM_SUB_CATEGORY, 
LEVEL1, LEVEL2, COUNT(*)
FROM
UPDATE_LEVEL1_LEVEL2
WHERE 
FLAG_DEFAULT = 'DEFAULT'
GROUP BY 
1,2,3,4,5) CONFIG
WHERE
config.INSTALLMENT_DESCRIPTION = ebx.INSTALLMENT_DESCRIPTION 
AND config.ITEM_CATEGORY = ebx.ITEM_CATEGORY 
AND config.ITEM_SUB_CATEGORY = ebx.SUB_CATEGORY 
AND ebx.LEVEL2 = ''

现在，我还需要更新DEFAULT_VALUE列，这样当更新查询运行时，它也会将DEFAULT-VALUE列中的所有记录更改为"0"；DEFAULT"；除非它在描述中也包含特殊词语，如"；经典；或"；GOLD"；。

Answer 1

您可以在这里使用CASE WHEN，就像在LEVEL2:列中一样

update UPDATE_LEVEL1_LEVEL2 ebx
set ebx.LEVEL1 = config.LEVEL1,
ebx.DEFAULT_VALUE = case when
ebx.DEFAULT_VALUE like  '%CLASSIC%' or ebx.DEFAULT_VALUE like '%GOLD%' then ebx.DEFAULT_VALUE
else 'DEFAULT'
end,
ebx.LEVEL2 = case
when config.ITEM_CATEGORY = 'LOOTCRATE' and config.=SUB_CATEGORY = 'ND' and ebx.ITEM_DESCRIPTION like '%CLASSIC%' then 'PLAYGROUND'
when config.ITEM_CATEGORY = 'LOOTCRATE' and config.=SUB_CATEGORY = 'ND' and ebx.ITEM_DESCRIPTION like '%GOLD%' then 'IGLOO'
else config.CATEGORY_LEVEL2
end
from (select INSTALLMENT_DESCRIPTION,ITEM_CATEGORY, ITEM_SUB_CATEGORY, LEVEL1, LEVEL2, count(*)
From UPDATE_LEVEL1_LEVEL2
WHERE FLAG_DEFAULT = 'DEFAULT'
GROUP BY 1,2,3,4,5) CONFIG
where config.INSTALLMENT_DESCRIPTION = ebx.INSTALLMENT_DESCRIPTION and
config.ITEM_CATEGORY = ebx.ITEM_CATEGORY and
config.ITEM_SUB_CATEGORY = ebx.SUB_CATEGORY and
ebx.LEVEL2 ='';