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.

3 Answers 3


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.

  • Thanks for the tip about everything being updated, if this is slow, I may take @Colin 't Hart's suggestion to have multiple update statements. Apr 12, 2013 at 11:35
  • You can mitigate that problem complete by making sure your ON and WHERE clauses filter out all rows where no changes are needed, but that may mean repeating all your conditions both in the SET clause and the WHERE clause (unless there is a simpler overall check that is 100% equivalent to all those conditions combined). At that point this method may still be more efficient, but the multiple updates method may be easier to maintain. Apr 12, 2013 at 11:40
  • Be aware also that updating a column to the same value will cause redo to be created, see orainternals.wordpress.com/2010/11/04/… Apr 12, 2013 at 11:57
  • @Colin: Aye, any update will pass through the DB's transaction log including updates that are esentially NoOps due to updating fields to have the same values they had before. As well as the potential for an immediate performance issue this could be an important factor if using replication, differential backups, log shipping, and so forth, as the extra row update operations will increase the space/bandwidth needed for those. Apr 12, 2013 at 12:52
  • Thanks to both of you for the tips, in my case the single update statement worked fine. Apr 12, 2013 at 14:59

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.

  • Thanks for the answer. I've added some more information to my question, I hope it's understandable. That's a good alternative with multiple update statements (I'd prefer one update statement, but I see there's an advantage there). Apr 12, 2013 at 11:32
  • This may be the answer I am looking for but do you mean to put update column_x = new_value_for_x where @val = 0. I may experiment on something like that. Looks funny. Isin't easier to do if val=0 begin update column_x = new_value_for_x etc.
    – CashCow
    Mar 23, 2017 at 16:15
update Practicing  -- table you will be updating
 set email = case -- column you will be updating
    when FName = 'Glenn' then '[email protected]'
       when FName = 'Riddick' then '[email protected]'
       when FName = 'Jeffrey' then 'sorcerer@wizcom'
       else email
  • This updating just a single column, while the asker wants to know how to update different columns depending on conditions. Sep 15, 2017 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.