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: 321
        
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