Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
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. –  newenglander Apr 12 '13 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. –  David Spillett Apr 12 '13 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/… –  Colin 't Hart Apr 12 '13 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. –  David Spillett Apr 12 '13 at 12:52
    
Thanks to both of you for the tips, in my case the single update statement worked fine. –  newenglander Apr 12 '13 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.

share|improve this answer
    
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). –  newenglander Apr 12 '13 at 11:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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