Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update if different/changed

Is it possible to perform an update statement in sql, but only update if the updates are different?

for example

if in the database, col1 = "hello"

update table1 set col1 = 'hello' 

should not perform any kind of update

however, if

update table1 set col1 = "bye" 

this should perform an update.

like image 929
nami Avatar asked Jul 13 '11 10:07

nami


People also ask

What is conditional update?

Conditional update applies to scenarios where high-concurrency applications are updated. In these scenarios, old_value may be updated by other clients. If you use conditional update, the current value is updated to new_value only when the current value is equal to old_value.

How do you update the same column with different values in SQL?

First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.

How do you update a column based on a filter of another column?

UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.

How do you update values based on conditions in SQL?

Update with conditionWHERE clause can be used with SQL UPDATE to add conditions while modifying records. Without using any WHERE clause, the SQL UPDATE command can change all the records for the specific columns of the table.


1 Answers

During query compilation and execution, SQL Server does not take the time to figure out whether an UPDATE statement will actually change any values or not. It just performs the writes as expected, even if unnecessary.

In the scenario like

update table1 set col1 = 'hello' 

you might think SQL won’t do anything, but it will – it will perform all of the writes necessary as if you’d actually changed the value. This occurs for both the physical table (or clustered index) as well as any non-clustered indexes defined on that column. This causes writes to the physical tables/indexes, recalculating of indexes and transaction log writes. When working with large data sets, there is huge performance benefits to only updating rows that will receive a change.

If we want to avoid the overhead of these writes when not necessary we have to devise a way to check for the need to be updated. One way to check for the need to update would be to add something like “where col <> 'hello'.

update table1 set col1 = 'hello' where col1 <> 'hello' 

But this would not perform well in some cases, for example if you were updating multiple columns in a table with many rows and only a small subset of those rows would actually have their values changed. This is because of the need to then filter on all of those columns, and non-equality predicates are generally not able to use index seeks, and the overhead of table & index writes and transaction log entries as mentioned above.

But there is a much better alternative using a combination of an EXISTS clause with an EXCEPT clause. The idea is to compare the values in the destination row to the values in the matching source row to determine if an update is actually needed. Look at the modified query below and examine the additional query filter starting with EXISTS. Note how inside the EXISTS clause the SELECT statements have no FROM clause. That part is particularly important because this only adds on an additional constant scan and a filter operation in the query plan (the cost of both is trivial). So what you end up with is a very lightweight method for determining if an UPDATE is even needed in the first place, avoiding unnecessary write overhead.

update table1 set col1 = 'hello' /* AVOID NET ZERO CHANGES */ where exists      (     /* DESTINATION */     select table1.col1     except     /* SOURCE */     select col1 = 'hello'     ) 

This looks overly complicated vs checking for updates in a simple WHERE clause for the simple scenerio in the original question when you are updating one value for all rows in a table with a literal value. However, this technique works very well if you are updating multiple columns in a table, and the source of your update is another query and you want to minimize writes and transaction logs entries. It also performs better than testing every field with <>.

A more complete example might be

update table1    set col1 = 'hello',        col2 = 'hello',        col3 = 'hello' /* Only update rows from CustomerId 100, 101, 102 & 103 */ where table1.CustomerId IN (100, 101, 102, 103) /* AVOID NET ZERO CHANGES */   and exists      (     /* DESTINATION */     select table1.col1            table1.col2            table1.col3     except     /* SOURCE */     select z.col1,            z.col2,            z.col3       from #anytemptableorsubquery z      where z.CustomerId = table1.CustomerId     ) 
like image 94
Dude0001 Avatar answered Sep 21 '22 14:09

Dude0001