Update if different/changed

Question

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.

Answer 1

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
    )

Answer 2

The idea is to not perform any update if a new value is the same as in DB right now

WHERE col1 != @newValue

(obviously there is also should be some Id field to identify a row)

WHERE Id = @Id AND col1 != @newValue

PS: Originally you want to do update only if value is 'bye' so just add AND col1 = 'bye', but I feel that this is redundant, I just suppose

PS 2: (From a comment) Also note, this won't update the value if col1 is NULL, so if NULL is a possibility, make it WHERE Id = @Id AND (col1 != @newValue OR col1 IS NULL).

Answer 3

If you want to change the field to 'hello' only if it is 'bye', use this:

UPDATE table1
SET col1 = 'hello'
WHERE col1 = 'bye'

If you want to update only if it is different that 'hello', use:

UPDATE table1
SET col1 = 'hello'
WHERE col1 <> 'hello'

Is there a reason for this strange approach? As Daniel commented, there is no special gain - except perhaps if you have thousands of rows with col1='hello'. Is that the case?

Answer 4

This is possible with a before-update trigger. In this trigger you can compare the old with the new values and cancel the update if they don't differ. But this will then lead to an error on the caller's site.
I don't know, why you want to do this, but here are several possibilities:

1. Performance: There is no performance gain here, because the update would not only need to find the correct row but additionally compare the data.
2. Trigger: If you want the trigger only to be fired if there was a real change, you need to implement your trigger like so, that it compares all old values to the new values before doing anything.

Answer 5

You need an unique key id in your table, (let's suppose it's value is 1) to do something like:

UPDATE table1 SET col1="hello" WHERE id=1 AND col1!="hello"

Answer 6

Old question but none of the answers correctly address null values.

Using <> or != will get you into trouble when comparing values for differences if there are is potential null in the new or old value to safely update only when changed use the is distinct from operator in Postgres. Read more about it here

Answer 7

CREATE OR REPLACE PROCEDURE stackoverflow([your_value] IN TYPE) AS
BEGIN
   UPDATE   [your_table] t
     SET t.[your_collumn] = [your_value]
   WHERE t.[your_collumn] != [your_value];
  COMMIT;


EXCEPTION
 [YOUR_EXCEPTION];

END stackoverflow;

Answer 8

I think this should do the trick for ya...

create trigger [trigger_name] on [table_name]
for insert 
AS declare  @new_val datatype,@id int;
select @new_val = i.column_name from inserted i;
select @id = i.Id from inserted i;
update table_name set column_name = @new_val
where table_name.Id = @id and column_name != @new_val;