Update multiple columns of a table conditionally in a single command

Question

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?

Answer 1

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.

Answer 2

Not very elegant, not very efficient, but in one query:

UPDATE names SET 
name =  CASE  name 
    WHEN 'Mic' THEN 'Micheal' ELSE name END,
client_name = CASE  client_name 
    WHEN 'Mic' THEN 'Micheal' ELSE client_name END,
requester_name= CASE  requester_name 
    WHEN 'Mic' THEN 'Micheal' ELSE requester_name END ;

This uses the abbreviated (Postgresql specific) syntax of CASE.

(BTW: I guess you meant 'Michael' instead of 'Micheal'?)