更新包含大量案例表达式的查询 - 如何避免空更新？

Sample Query

示例查询

UPDATE table_name tn SET 
        col1 = CASE WHEN col1 = 'x' THEN 'X' WHEN col1 = 'y' THEN 'Y' ELSE 'Z' END,
        col2 = CASE WHEN col2 = 'x' THEN 'X' WHEN col2 = 'y' THEN 'Y' ELSE 'Z' END,
        col3 = CASE WHEN col3 = 'x' THEN 'X' WHEN col3 = 'y' THEN 'Y' ELSE 'Z'
        END
        FROM temp_table tt
        WHERE tn.id = tt.id AND 
    ( col1 IS DISTINCT FROM (CASE WHEN col1 = 'x' THEN 'X' WHEN col1 = 'y' THEN 'Y' ELSE 'Z' END) 
OR col2 IS DISTINCT FROM (CASE WHEN col2 = 'x' THEN 'X' WHEN col2 = 'y' THEN 'Y' ELSE 'Z' END) 
OR (col3 IS DISTINCT FROM (CASE WHEN col3 = 'x' THEN 'X' WHEN col3 = 'y' THEN 'Y' ELSE 'Z' END) )

For the above query to avoid empty updates, we can formulate the query as

对于上述查询以避免空更新，我们可以将查询表示为

WITH tmp AS
(
SELECT id, col1 = CASE WHEN col1 = 'x' THEN 'X' WHEN col1 = 'y' THEN 'Y' ELSE 'Z' END,
            col2 = CASE WHEN col2 = 'x' THEN 'X' WHEN col2 = 'y' THEN 'Y' ELSE 'Z' END column2,
            col3 = CASE WHEN col3 = 'x' THEN 'X' WHEN col3 = 'y' THEN 'Y' ELSE 'Z' AS coloumn3
FROM temp_table
)
UPDATE table_name tn SET col1 = tmp.column1, col2 = tmp.column2, col3 = tmp.column3
FROM tmp
WHERE tmp.id = tn.id AND
      (tn.col1 IS DISTINCT FROM tmp.column1 OR
       tn.col2 IS DISTINCT FROM tmp.column2 OR
       tn.col3 IS DISTINCT FROM tmp.column3
      )

Is there a better way to do this?

有一个更好的方法吗？

Thanks, Shankar.

谢谢你，尚卡尔。

1 个解决方案

#1

In Postgres, you can use row constructors:

在Postgres中，您可以使用行构造函数：

WITH tmp AS (
      SELECT id, col1 = CASE WHEN col1 = 'x' THEN 'X' WHEN col1 = 'y' THEN 'Y' ELSE 'Z' END,
             col2 = CASE WHEN col2 = 'x' THEN 'X' WHEN col2 = 'y' THEN 'Y' ELSE 'Z' END column2,
             col3 = CASE WHEN col3 = 'x' THEN 'X' WHEN col3 = 'y' THEN 'Y' ELSE 'Z' AS coloumn3
      FROM temp_table
     )
UPDATE table_name tn
    SET (col1, col2, col3) = (tmp.column1, tmp.column2, tmp.column3
FROM tmp
WHERE tmp.id = tn.id AND
      (tn.col1, tn.col2, tn.col3) IS DISTINCT FROM (tmp.column1, tmp.column2, temp.column3);