Update If Different/Changed

Update if different/changed

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.

Update value only if changed in sql

Assuming that the table is called scores and the fields id and relevance, you can update a single row like that:

UPDATE scores
SET relevance = <new value>
WHERE id = 42
  AND relevance <> <new value>;

That will update the row only if the value has changed.

Merge - Only update if values have changed

WHEN MATCHED can have AND . Also, no need to update EMP_ID .

...
 WHEN MATCHED AND (trg.First_Name <> src.First_Name 
   OR trg.Last_Name <> src.Last_Name) THEN UPDATE
   SET 
   [VERSION] = tgt.VERSION + 1 
    ,First_Name = src.First_Name
    ,Last_Name = src.Last_Name
 ...

If Last_Name or First_Name are nullable, you need to take care of NULL values while comparing trg.Last_Name <> src.Last_Name , for instance ISNULL(trg.Last_Name,'') <> ISNULL(src.Last_Name,'')

SQL Server - Update other table only if column values change

Something like below would work,

CREATE TRIGGER after_update_table1
AFTER UPDATE ON table1 
FOR EACH ROW 
BEGIN 
     IF OLD.val1<> new.val1 THEN
         /**
            -- do operations on table2
        **/ 
     END IF;
END;

-- if val1 allows null we need to check by adding more conditions to the if clause like,

IF((OLD.val1 IS NULL AND NEW.val1 IS NOT NULL) OR (OLD.val1 IS NOT NULL AND NEW.val1 IS NULL) OR (OLD.val1<>NEW.val1)) THEN

/**
-- operations on table2
**/
END IF;

Update the changed row of target table only using source table if there is any value mismatch between 2 columns

Try this one (in case you track the changes for all 3 columns - c1, c2 and c3):

MERGE INTO T1
USING (
  select id, c1, c2, c3 from T2
  minus
  select id, c1, c2, c3 from T1
) T2
ON (T1.ID = T2.ID)
WHEN MATCHED THEN
UPDATE SET T1.C1 = T2.C1,T1.C2 = T2.C2,T1.C3 = T2.C3,T1.update_timestamp=SYSDATE;

MySQL: update a field only if condition is met

Yes!

Here you have another example:

UPDATE prices
SET final_price= CASE
   WHEN currency=1 THEN 0.81*final_price
   ELSE final_price
END

This works because MySQL doesn't update the row, if there is no change, as mentioned in docs:

If you set a column to the value it currently has, MySQL notices this
and does not update it.

---

Related Topics