6

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.

Community
  • 1
  • 1
Josh
  • 1,574
  • 1
  • 15
  • 36
  • 3
    Short answer is no. Personally I hate the "1=1" stuff and prefer to write smarter logic that knows whether to prepend a `,` or an `and` depending on how many expressions need to be concatenated. You might want to watch the parsing load that your system will have to deal with - make sure you always generate the SET clause with the columns in the same order each time, to minimise this. – Jeffrey Kemp Jun 14 '16 at 03:18
  • This works: `DECLARE @dummy VARCHAR(1) = 'a'; UPDATE my_table SET @dummy = @dummy WHERE 1 = 1 AND my_second_column = 'My Second Value'` . However, this does not: `DECLARE @dummy VARCHAR(1) = 'a'; UPDATE my_table SET my_first_column = 'My First Value', @dummy = @dummy WHERE 1 = 1 AND my_second_column = 'My Second Value'` – Dennis T --Reinstate Monica-- Aug 06 '18 at 07:21

2 Answers2

2

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.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • 1
    This would complicate things if the underlying Oracle database has many `UPDATE` triggers, as Oracle does not distinguish between `UPDATE mytable SET mycolumn = mycolumn` and 'UPDATE mytable SET mycolumn = mynewvalue` when determining whether a trigger should be fired. – David Faber Jun 06 '16 at 22:00
  • +1 for a good answer that unfortunately does not meet my use case. I updated the original question with more criteria, sorry for not being clear. – Josh Jun 07 '16 at 12:26
  • @Josh "[we] found the only way to meet performance goals was to just send in data that needs to be updated" You are still sending only the data that needs to be updated, the rest of columns would be `NULL`s, so they would take only a tiny amount of bandwidth. Being able to prepare the statement, rather than using different SQL each time, should save you quite a bit of time. – Sergey Kalinichenko Jun 07 '16 at 12:38
  • Won't passing in columns that don't change still cause the database to spend time "writing" the value down, even though it's the same value? For example, what if one of the columns was a large chunk of text? – Josh Jun 07 '16 at 13:43
  • 1
    @Josh According to [this article](https://orainternals.wordpress.com/2010/11/04/does-an-update-statement-modify-the-row-if-the-update-modifies-the-column-to-same-value/), Oracle has some optimizations in place for situations when an update does not change the value. For example, when the column is indexed, Oracle would not touch the index. It would write to undo and redo logs, though, but it is not clear from the article if this is something that happens to all columns of the updated row, or only to the columns mentioned in the update. The best way to find out is profiling, of course. – Sergey Kalinichenko Jun 07 '16 at 13:59
  • Thank you for the interesting information, I will go review that. – Josh Jun 07 '16 at 14:21
  • With a normal heap table, when it does an update, Oracle writes a new version of the entire block in which the record resides, so there is negligible performance difference between updating one column vs all columns. The difference between setting one column vs. all columns is (apart from the index maintenance as mentioned above) that any table triggers that are limited to certain columns will always fire in the "all columns" scenario regardless of whether they were actually changed or not. – Jeffrey Kemp Jun 14 '16 at 03:22
  • Of course, things can get less simplistic in cases where the new version of the record is significantly larger than the original version, if Oracle has to do row chaining to accommodate the row's larger size. – Jeffrey Kemp Jun 14 '16 at 03:23
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.

Dariusz
  • 21,561
  • 9
  • 74
  • 114
  • A single row update will never lock the entire table in Postgres. Regardless of the WHERE condition or which columns are updated –  Jan 08 '18 at 07:16
  • @a_horse_with_no_name thanks for the remark. It happens with Oracle and caused me a serious problem, so I decided to update the answer. – Dariusz Jan 08 '18 at 09:07
  • Oracle will also never lock the entire table when doing an `UPDATE` –  Jan 08 '18 at 09:16
  • Thanks for your remark, I now see I may have overextended my conclusions. The case I encountered was slightly different. Please take a look at https://asktom.oracle.com/Misc/something-i-recently-unlearned.html – Dariusz Jan 08 '18 at 11:27