Partial update based on condition in postgres

Question

I would like to make an update to a table, and the columns updated is dependent on a condition. I already know how to make a conditional WHERE clause like so:

UPDATE table SET colA='apple', colB='banana' WHERE colC='fruits

However I would like to base the update on a condition, such that if the conditional is false, only colA will update. Something to the extent of the following:

UPDATE table SET colA='apple', (if $1=true colB='banana') WHERE colC='fruits

The only resources I'm finding are pertaining to the first example. Is this possible in Postgres? I would like to avoid making separate queries for this type of condition.

Tim Biegeleisen · Accepted Answer · 2019-10-26 06:54:48Z

9

You could use CASE expressions to control the logic of updating the B column:

UPDATE table
SET
    colA = 'apple',
    colB = CASE WHEN $1 = true THEN 'banana' ELSE colB END
WHERE
    colC = 'fruits';

When the $1 flag be set to true, the B column would be updated to banana, otherwise the update would just set to the current value, which is effectively a no-op.

answered Oct 26, 2019 at 6:54

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

richyen · Accepted Answer · 2019-10-26 07:26:39Z

1

A CASE statement might help you here:

UPDATE table
SET colA = 'apple',
    colB = CASE WHEN $1 = true THEN 'banana' ELSE colB END
WHERE colC = 'fruits';

However, there may be risk of SQL injection, in which case you may be better off having two separate queries, selected by your application logic.

answered Oct 26, 2019 at 7:26

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

Partial update based on condition in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related