Use CASE to select columns in UPDATE query?

Question

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so:

SELECT CASE WHEN val = 0 THEN column_x
            WHEN val = 1 THEN column_y
            ELSE 0
       END AS update, ...

Is something similar at all possible when performing an UPDATE query in Postgres (i.e. choose which columns should be updated)? I assume not since I couldn't find anything about this, but maybe someone has a clever alternative (besides using a procedure or updating each column using a CASE to determine if the value of the column should be assigned a new value or simply reassigned the existing value). If there is no easy alternative, I'll of course accept that as an answer as well.

Extra info: In my case I have 14 potential columns that may be updated, with only one being updated per matching row (the table to be updated is joined with another in the query). The amount of rows to update will most likely vary, could be dozens or hundreds. I believe indexes are in place for the joining conditions.

Answer 1

If you specify a column should be updated then it will always be updated, but you can change the value you put in conditionally and put back the original values depending on your conditions. Something like:

UPDATE some_table
SET    column_x = CASE WHEN should_update_x THEN new_value_for_x ELSE column_x END
     , column_y = CASE WHEN should_update_y THEN new_value_for_y ELSE column_y END
     , column_z = CASE WHEN should_update_z THEN new_value_for_z ELSE column_z END
FROM   ...

So if the conditions are not right for an update to a particular column you just feed back it's current value.

Do note that every row matched will see an update (even if all the columns end up getting set to the values they already have) unless you explicitly gate this circumstance in you filtering ON and WHERE clauses, which could be a performance problem (there will be a write, indexes will be updated, appropriate triggers will fire, ...) if not mitigated.

Answer 2

How many different combinations of columns to update do you have? How many rows of the entire table will be updated? Are indexes in place for fast access to rows to update?

Depending on the answers to these questions you may be able to execute multiple update statements, one for each column that you wish to update and place the condition on that column's value in the where clause of the update so that zero rows are updated if that column has the wrong value.

Try and think set-based, don't assume that update needs to update a single row found by the primary key.

Answer 3

update Practicing  -- table you will be updating
 set email = case -- column you will be updating
    when FName = 'Glenn' then 'Ace@Assasin.com'
       when FName = 'Riddick' then 'fallguy@Drunk.com'
       when FName = 'Jeffrey' then 'sorcerer@wizcom'
       else email
    end