update equivalent of WHERE '1' = '1'

Question

When dynamically generating WHERE clauses for my sql, I like to hardcode WHERE '1' = '1' into the sql so I don't have to track whether to prepend AND to each following clause. This is well documented in many places, such as this stackoverflow question.

Is there an equivalent pattern for dynamically generating the SET clause for UPDATE statements? I rather not keep track of whether I need to prepend a comma or not. In case there aren't any general solutions, this will be for interacting with an oracle database over jdbc.

EDIT For my particular use case, I will need to dynamically change which columns are being set. So any solution which requires the query to contain all columns being set is a no go. We have a table with 20+ columns, but only 3 or 4 will change at any given time. We ran some load tests and found the only way to meet performance goals was to just send in data that needs to be updated. Now I'm just trying to write pretty code to do so.

Answer 1

One way to avoid keeping track of column count for the purpose of appending commas is to always assign all possible columns, and pass a set of control variables to decide if a column should be assigned or not:

UPDATE MyTable
SET
    col1 = CASE ? WHEN 1 THEN ? ELSE col1 END
,   col2 = CASE ? WHEN 1 THEN ? ELSE col2 END
,   col3 = CASE ? WHEN 1 THEN ? ELSE col3 END
WHERE
    ... -- condition goes here

Parameters at odd indexes are flags that you pass to indicate that the corresponding column must be set. Parameters at their corresponding even indexes are values that you want to set, or NULL if you are not setting the corresponding field.

This approach doubles the number of JDBC parameters that you need to pass, but in return you get a statement where positions of all columns are fixed, so you can prepare and reuse it instead of building it dynamically.

Answer 2

First of all, it's better to not execute a query if nothing's changed.

But if you have to, or doing it other way is much more costly, you can usually do this:

UPDATE 
  MY_TABLE
SET
  <if test="xx">  COL1='VAL1',</if>
  ID=ID
where 1=1
  COL1 like 'VAL%';

With ID=ID acting as a noop.

Remember, that this will still execute the update with all "side effects", like running triggers.