Search code examples
postgresqlquery-optimization

using IF statement with update clause in pgsql

I want to add a condition in update I am really not sure how to do it. I want to do following:

UPDATE 
    xyz_table
SET
    cap = some_text, IF (some_text IS NULL) THEN some_text = alternate_text ENDIF
WHERE
    alternate_text <> '' AND 
    cap IS NULL AND 
    some_relation_id IN ( 50, 51, 52  );

If there is really any way to do this, please suggest else I think I will need to execute same query twice to achieve this. Thanks in Advance :)

Solution

  • You can use case or coalesce(). This is the set statement:

    SET cap = some_text, 
    some_text = COALESCE(some_text, alternate_text)