CASE, WHEN, THEN on UPDATE

Question

I have this issue where if there is no record in copy_on_write.id then the UPDATE listings SET images = (SELECT images FROM new_vals) runs and wipes out listings.images with nothing.

So, I am trying to use a condition to only run the UPDATE listings if copy_on_write.images exist.

right now I get:

psql:queries/copy-to-source.sh:20: ERROR:  syntax error at or near "CASE"
LINE 10:       CASE WHEN images <>

WITH
  new_vals AS (
    SELECT *
    FROM copy_on_write
    WHERE copy_on_write.posted_by = 102550922::text
      AND copy_on_write.id = 4
  ),

  updates AS (
    SELECT images FROM new_vals,
      CASE WHEN images <> ''
        THEN UPDATE listings SET images = (SELECT images FROM new_vals)
      END
  )
SELECT internal_id FROM new_vals

score 4 · Accepted Answer · answered Jul 08 '17 at 07:36

You can use updates CTE like this:

...
updates AS (
    UPDATE listings SET 
    images = new_vals.images
    FROM new_vals
    WHERE new_vals.images <> ''
)
....

Note, that:

Your new_vals CTE should always return maximum one record, otherwise this won't works correct.
Also this not updates listings table, if new_vals returns images column, but it is empty string (or null). If in such cases you need run update anyway, then remove WHERE new_vals.images <> '' at all.
And also, this statement will update all listings.images records. Do you really want this?

CASE, WHEN, THEN on UPDATE

1 Answers1