0

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.

  • WHERE id=$2 AND (${assignmentList}) IS NOT NULL. Or WHERE id=$2 AND LENGTH('${assignmentList}')>0. Or something similar... – Akina Mar 26 '19 at 18:17
  • 3
    I cannot understand why you cannot to check ${assignmentList} on the application level, and do not execute the query at all when assignmentList it is empty. – Akina Mar 26 '19 at 18:21
  • 1
    Context: github.com/gajus/slonik/issues/46 @Akina I think that the best API is for the generator to throw an error if empty assignment list is provided and make the user guard to make sure that does not happen/ let him not to execute the query. I was checking if there is a generic no-op pattern, though. – Gajus Mar 26 '19 at 18:23
  • I could do UPDATE foo SET id=id WHERE id=$2 or any other known column, but that would actually perform an update of the row. – Gajus Mar 26 '19 at 18:24
  • 2
    Please confirm that you assemble that variable in a way in cannot be something like col = 1; DROP DATABASE this;-- – dezso Apr 18 '19 at 8:36
1

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.

| improve this answer | |
0

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
;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.