postgresql - 用两个条件更新一列

Question

我想做以下事情：

UPDATE table1 t1
SET column1 = 10 * t2.column1
FROM table2 t2
WHERE t1.column2 = 'yyy'

UPDATE table1 t1
SET column1 = 10 * t2.column2
FROM table2 t2
WHERE t1.column2 = 'zzz'

是否可以将其保存在一个语义块中？

标签： postgresql

Answer 1

您CASE WHEN ..只能在具有有效条件（'yyy' OR 'zzz'）的行上使用和应用更新

    UPDATE table1 t1 
    SET    column1 = CASE 
            WHEN t1.column2 = 'yyy' THEN column1 = 10 * t2.column1 
            WHEN t1.column2 = 'zzz' THEN column1 = 10 * t2.column2 
    end
    FROM   table2 t2
    WHERE t1.column2  IN ( 'yyy','zzz')