Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Suppose we have a simple update query:
UPDATE foo
SET
bar=$1
WHERE id=$2
where bar=$1 is generated based on user's input and it might be null, e.g.
UPDATE foo
SET
${assignmentList}
WHERE id=$2
How do I make this update operation into a no-op in case assignmentList variable is empty?
I have tried using 1=1, i.e.
UPDATE foo
SET
1=1
WHERE id=$2
but this gives a syntax error.
It is only slightly more code to skip the UPDATE if there is nothing to do. And the code is much cleaner. And less kludgy.
While Rick's answer addresses the root cause here, I would like to answer the more general question "How to write a no-op UPDATE query?"
You can write an UPDATE that does not update any row by providing a WHERE clause that is never true.
UPDATE foo
SET bar = 'value'
WHERE 1 <> 1
;
WHERE id=$2 AND (${assignmentList}) IS NOT NULL. OrWHERE id=$2 AND LENGTH('${assignmentList}')>0. Or something similar...${assignmentList}on the application level, and do not execute the query at all when assignmentList it is empty.UPDATE foo SET id=id WHERE id=$2or any other known column, but that would actually perform an update of the row.col = 1; DROP DATABASE this;--