Oracle - UPSERT with update not executed for unmodified values

Posted by Buthrakaur on Stack Overflow See other posts from Stack Overflow or by Buthrakaur
Published on 2010-05-26T09:27:12Z Indexed on 2010/05/26 9:41 UTC
Read the original article Hit count: 230

Filed under:
|
|
|

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;

© Stack Overflow or respective owner

Related posts about sql

Related posts about Oracle