mysql - 如果不是，请更新sql查询以合并多个查询

表中有空列prod2
我试图在栏中与现有的纵横栏进行比较。我知道如何编写单个查询，但有谁能帮助我编写一个执行所有操作的单个查询。

update FINANCE
set prod2 = 'Vegetable'
where PRODUCT='VEG'

update FINANCE
set prod2 = 'Hot Dog'
where PRODUCT='HOT DOG'

update FINANCE
set prod2 = 'Cookie'
where PRODUCT='COOKIE'

update FINANCE
set prod2 = 'Other'
where PRODUCT= NULL

最佳答案

案例陈述是你最好的选择：

update FINANCE
set prod2 = CASE
    WHEN PRODUCT = 'VEG'
        THEN 'Vegetable'
    WHEN PRODUCT = 'HOT DOG'
        THEN 'Hot Dog'
    WHEN PRODUCT = 'COOKIE'
        THEN 'Cookie'
    WHEN PRODUCT IS NULL
        THEN 'Other'
    -- Optional - just in case there is a value in Product that is not one of these values
    -- ELSE Product
END