I have a table that contains the columns
. 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
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
necessary, and be generally very slow.
BTW, either form of the
statement is good here.