Updating multiple columns in a table conditionally in a single command

微信扫一扫,分享到朋友圈

Updating multiple columns in a table conditionally in a single command

I have a table that contains the columns name
, client_name
and requester_name
. I need to update values of these columns from “Mic” to “Michael”.

Here are some records that should be updated:

name  | client_name |  requester_name
------+-------------+----------------
Mic   | Jerry       | Jack
Jack  | Mic         | Mic
Jerry | Jack        | Mic

I tried the following query:

UPDATE names
SET name='Michael', client_name='Michael', requester_name='Michael'
WHERE name='Mic' OR client_name='Mic' OR requester_name='Mic';

This query makes all columns change all names to ‘Michael’.

What should the query look like to only apply changes where applicable?

It would be wise to add a WHERE
clause.

UPDATE names
SET    name = CASE WHEN name = 'Mic' THEN 'Michael' ELSE name END
      ,client_name = CASE WHEN client_name = 'Mic' THEN 'Michael'
                     ELSE client_name END
      ,requester_name = CASE WHEN requester_name = 'Mic' THEN 'Michael'
                        ELSE requester_name END
WHERE 'Mic' IN (name, client_name, requester_name);

Else, the whole table will be updated unconditionally. Updates that change values to the same value are still updates creating dead rows, triggering triggers and so on. While the resulting rows would not be wrong, it would still bloat the table to twice its size, making VACUUM
necessary, and be generally very slow.

BTW, either form of the CASE
statement is good here.

微信扫一扫,分享到朋友圈

Updating multiple columns in a table conditionally in a single command

Ask Siri to Define 'Mother' and You'll Get a Surprisingly Explicit Response

上一篇

怎样才是够格的价格屠夫?

下一篇

你也可能喜欢

Updating multiple columns in a table conditionally in a single command

长按储存图像,分享给朋友