mkohanek mkohanek - 1 month ago 12
SQL Question

Need to insert row in table if primary keys do not exist, otherwise update existing row

I am trying to run a bunch of SQL that will check if a composite primary key value exists in the table. if it exists, then update the table, otherwise insert new values. I thought a merge statement was the way to do this. But the merge I have written gets 0 rows merged when I run it. I would expect the NOT MATCHED condition to occur in the following example, but it does not. Here is the script to test it and the MERGE I am trying:

CREATE TABLE curformat (storeent_id INTEGER,
setccurr VARCHAR2(3),
roundingmultiple INTEGER,
numbrusg_id INTEGER,
roundingmethod VARCHAR2(1),
decimalplaces INTEGER,
MINAPPROVEAMOUNT NUMBER,
optcounter INTEGER);

INSERT INTO curformat VALUES(-1, 'USD', 1, -1, 'R', 2, NULL, NULL);

----------------------------------------------------------------------

MERGE INTO curformat cf
USING (SELECT *
FROM curformat
WHERE storeent_id = 10201
AND setccurr = 'USD'
AND numbrusg_id = -1
) current_curformat
ON (cf.storeent_id = current_curformat.storeent_id
AND cf.setccurr = current_curformat.setccurr
AND cf.numbrusg_id = current_curformat.numbrusg_id
)
WHEN MATCHED THEN UPDATE
SET roundingmultiple = 1,
roundingmethod = 'R',
decimalplaces = 2,
minapproveamount = NULL
WHEN NOT MATCHED THEN INSERT (storeent_id, setccurr, roundingmultiple, numbrusg_id, roundingmethod, decimalplaces, minapproveamount)
VALUES (10201, 'USD', 1, -1, 'R', 2, NULL
);


In this case, since the combination of

WHERE storeent_id = 10201
AND setccurr = 'USD'
AND numbrusg_id = -1


is not true, I would have thought the WHEN NOT MATCHED would be run.
Can anyone help me understand what I am not understanding here? I imagine I am mis-using the MERGE statement?

Answer

sstan has already explained why your version of MERGE will not work. When you want to update / insert into curformat, you don't pull rows from that table. You have "new" rows that you either want to insert into your table or you want to use for update.

So - it is actually easy to fix your MERGE statement. Pay close attention to what the "delta" is though (current_format used in the USING clause). In this case, it is a row created from "thin air" (which in Oracle means "created from the dual table").

MERGE INTO curformat cf
USING (SELECT 10201 as storeent_id,
              'USD' as setccurr,
              -1    as numbrusg_id
       FROM   dual
      ) current_curformat
      ON (cf.storeent_id = current_curformat.storeent_id
          AND cf.setccurr = current_curformat.setccurr
          AND cf.numbrusg_id = current_curformat.numbrusg_id
         )
 WHEN MATCHED THEN UPDATE
      SET roundingmultiple = 1,
          roundingmethod = 'R',
          decimalplaces = 2,
          minapproveamount = NULL
 WHEN NOT MATCHED THEN INSERT (storeent_id, setccurr, roundingmultiple, numbrusg_id,
                                     roundingmethod, decimalplaces, minapproveamount)
      VALUES (10201, 'USD', 1, -1, 'R', 2, NULL
             );

This will do exactly what you want.

However, here we hardcoded the values to be inserted when there is no match. That is a very poor practice. The correct way to do this is (just showing the last two rows, which are the only ones changed):

      VALUES (current_format.storeent_id, current_format.setccurr,
                                 1, current_format.numbrusg_id, 'R', 2, NULL
             );
Comments