Oracle - UPSERT with update not executed for unmodified values
- by Buthrakaur
I'm using following update or insert Oracle statement at the moment:
BEGIN
UPDATE DSMS
SET SURNAME = :SURNAME, FIRSTNAME = :FIRSTNAME, VALID = :VALID
WHERE DSM = :DSM;
IF (SQL%ROWCOUNT = 0) THEN
INSERT INTO DSMS
(DSM, SURNAME, FIRSTNAME, VALID)
VALUES
(:DSM, :SURNAME, :FIRSTNAME, :VALID);
END IF;
END;
This runs fine except that the update statement performs dummy update if the data is same as the parameter values provided. I would not mind the dummy update in normal situation, but there's a replication/synchronization system build over this table using triggers on tables to capture updated records and executing this statement frequently for many records simply means that I'd cause huge traffic in triggers and the sync system.
Is there any simple method how to reformulate this code that the update statement wouldn't update record if not necessary without using following IF-EXISTS check code which I find not sleek enough and maybe also not most efficient for this task?
DECLARE
CNT NUMBER;
BEGIN
SELECT COUNT(1) INTO CNT FROM DSMS WHERE DSM = :DSM;
IF SQL%FOUND THEN
UPDATE DSMS
SET SURNAME = :SURNAME, FIRSTNAME = :FIRSTNAME, VALID = :VALID
WHERE DSM = :DSM
AND (SURNAME != :SURNAME
OR FIRSTNAME != :FIRSTNAME
OR VALID != :VALID);
ELSE
INSERT INTO DSMS
(DSM, SURNAME, FIRSTNAME, VALID)
VALUES
(:DSM, :SURNAME, :FIRSTNAME, :VALID);
END IF;
END;
