-
Notifications
You must be signed in to change notification settings - Fork 924
How to handle a row UPDATE with multiple updateable fields, but only a subset of them need updating #1149
-
The only examples I have been able to find are ones where only one field is updated. I expanded the examples for multiple fields, but I'm not sure how to code it to correctly handle when all of the updateable fields are not being updated in a single update, just a subset of those fields.
For example, I have a table where the "name" field and "description" field are both updateable, but in an update I may want to only change the name. Currently, the way I expanded the single field examples, if I don't provide a value for the description, it changes it in the record to empty string rather than retaining the value it had before the update. Now, expand this to a table that might have four or ten or n updateable fields. What is the proper way to implement this? Thanks.
Note: According to spellchecker, updateable doesn't appear to be a real word. Until now. Consider it officially anointed as a full-fledged word and, of course, copyrighted by me, with royalties payable in perpetuity: updateable©
Beta Was this translation helpful? Give feedback.
All reactions
This is how I do it - based off an excellent blog post by @brandur.
-- name: UpdateWorkspace :exec
UPDATE workspaces
SET
state = CASE WHEN @state_do_update::boolean
THEN @state::workspace_state ELSE state END
WHERE
id = @id::uuid;
Replies: 2 comments
-
This is how I do it - based off an excellent blog post by @brandur.
-- name: UpdateWorkspace :exec
UPDATE workspaces
SET
state = CASE WHEN @state_do_update::boolean
THEN @state::workspace_state ELSE state END
WHERE
id = @id::uuid;
Beta Was this translation helpful? Give feedback.
All reactions
-
Since this question was posted, there is another way to do partial updates:
https://docs.sqlc.dev/en/latest/howto/named_parameters.html#nullable-parameters
-- name: UpdateAuthor :one
UPDATE author
SET
name = coalesce(sqlc.narg('name'), name),
bio = coalesce(sqlc.narg('bio'), bio)
WHERE id = sqlc.arg('id')
RETURNING *;
In this case only the values that are passed to query are updated.
Beta Was this translation helpful? Give feedback.