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
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?
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments