How to "Smart Update" an entry based on provided data?-CodePudding

Is it possible to write an SQL UPDATE that only changes columns if data is actually provided?

Let's say I'm updating a post. A post has an id, author, title and text. When updating a post, either or both of title and text can change.
Is there an SQL query which can dynamically change the UPDATE based on the provided data? It would maybe look something like this in Nodejs pg:

const result = await pgClient.query("UPDATE post SET title = $1, text = $2 WHERE userId = $3", 
                                    ["new title", undefined, "12345"]);

with an additional clause saying something like "if undefined, do not update."

CodePudding user response：

Use COALESCE():

UPDATE post
SET    title = COALESCE($1, title)
     , text  = COALESCE($2, text)
WHERE  userId = $3
AND   (title <> $1 OR text <> $2);

Plus, add an additional WHERE clause to skip updates that would not change anything (but cost the same).
In your case at least one of the non-null parameters must actually differ to have an impact.
If columns themselves can be NULL, use instead:

...
AND   ($1 IS NOT NULL AND title IS DISTINCT FROM $1
    OR $2 IS NOT NULL AND text IS DISTINCT FROM $2);

See:

How do I (or can I) SELECT DISTINCT on multiple columns?

CodePudding user response：

how about something like this:

UPDATE  post
SET title = CASE WHEN $1 IS NULL THEN title ELSE $1 END
....