5

Is it possible to report an error (raise exception or anything) in merge's when-not-matched-then clause? I'm aiming for something like

WHEN NOT MATCHED THEN RAISE NO_DATA_FOUND;

Or anything that will tell the caller that script failed.

2
  • That's basically an UPDATE statement, not MERGE. You may want to explain your use case a bit.
    – mustaccio
    May 12, 2017 at 15:22
  • Requirement change. The existing script I'm working on should now not make insert, and should report failure, hence the question. However it's large, so I'm looking for solution so not to turn it all over into a plain update. May 12, 2017 at 16:20

3 Answers 3

7

You can do this using a trigger.

Note: This is HORRIBLE, but I can't think of another way of achieving this.

Test tables:

create table testmerge
(
col1 number primary key ,
col2 number
);

insert into testmerge values ( 1, 1 );

create table testmerge2
(
col1 number primary key,
col2 number 
);

insert into testmerge2 values ( 2, 2 );

Trigger on merge table:

create or replace trigger testmergeerror_btrig
  before insert on testmerge
  for each row
begin
if :new.col1 = -99999999999 and :new.col2 = -99999999999 
then
    raise_application_error(-20000,'Your error message');
end if;
end;
/

Merge:

MERGE INTO testmerge t1
    USING testmerge2 t2
    ON (t1.col1 = t2.col1)
  WHEN MATCHED THEN
    UPDATE SET t1.col2 = t2.col2
WHEN NOT MATCHED THEN
insert(col1,col2) values (-99999999999,-99999999999);

Basically, when a row doesn't match, we set some values (that your application will never use, yes I know it is a horrible way of doing it!) in the INSERT that the trigger looks for, then fires an error.

Test case:

SQL> MERGE INTO testmerge t1
  2  USING testmerge2 t2
  3  ON (t1.col1 = t2.col1)
  4  WHEN MATCHED THEN
  5  UPDATE SET t1.col2 = t2.col2
  6  WHEN NOT MATCHED THEN
  7  insert(col1,col2) values (-99999999999,-99999999999);
USING testmerge2 t2
      *
ERROR at line 2:
ORA-20000: Your error message
ORA-06512: at "PHIL.TESTMERGEERROR_BTRIG", line 4
ORA-04088: error during execution of trigger 'PHIL.TESTMERGEERROR_BTRIG'


SQL>
3
  • Thank you for reply. Unfortunately, I'm having trouble integrating trigger creation part into my script. How exactly should it be with the merge follow-up in the same file? May 12, 2017 at 10:52
  • The above MERGE is just an example of how you would have to modify your existing MERGE code to account for the trigger. Your own code will obviously be different
    – Philᵀᴹ
    May 12, 2017 at 10:53
  • I understand that. It's just that having both trigger and merge in one file like this: > CREATE OR REPLACE TRIGGER ... END IF; END; \ MERGE INTO ... makes sqlplus print numbers in console. I figure that it has something to do with script termination but can't figure out how to fix. May 12, 2017 at 10:59
2

My feeling doing a Merge is the same as doing a select statement, then inserting data that does not yet exist in the target table and updating data where the primary key is already there. It sounds like you are saying that all of the data should be there in advance and you are just updating some columns based on new data. You could do a minus query first and if that returns rows then raise your exception. Otherwise you would do either a merge command or simply an update command. Row level triggers tend not to scale to large volumes of data. if you are doing batch processing, then you probably only have one process changing the data at a time, so worrying about concurrency might not be relevant. Why is it an error to not have data?

1
  • Its a design change. The table for which the script was developed ,is no longer allowed to have rows inserted directly, since a side app to generate and keep track of serial keys is introduced. So should sql script do inserts itself, things will break. May 15, 2017 at 7:36
0

Instead of Phil's sentinel value approach, and if you're using SQL Server (if Google lead you here even though you explicitly searched for SQL Server) I recommend using the OUTPUT clause (which unfortunately Oracle doesn't have):

DECLARE @MyTable TABLE(MyId INT, MyValue VARCHAR(MAX))
INSERT INTO @MyTable(MyId, MyValue)
VALUES (1, NULL), (2, NULL), (3, NULL)

DECLARE @Result TABLE
(
     [Action] VARCHAR(20)
    ,DeletedId INT
    ,DeletedMyValue VARCHAR(MAX)
    ,InsertedId INT
    ,InsertedMyValue VARCHAR(MAX)
)

MERGE @MyTable AS TARGET
USING (SELECT 1, 'test1' UNION ALL SELECT 4, 'test4') AS SOURCE(MyId, MyValue)
ON TARGET.MyId = SOURCE.MyId
WHEN MATCHED
    THEN UPDATE SET
        TARGET.MyValue = SOURCE.MyValue
WHEN NOT MATCHED BY TARGET
    THEN INSERT(MyId, MyValue) VALUES(SOURCE.MyId, SOURCE.MyValue)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action AS [Action], deleted.*, inserted.*
INTO @Result;

IF EXISTS(SELECT * FROM @Result)
BEGIN
    DECLARE @ErrorMessage VARCHAR(MAX) = NULL
    SELECT @ErrorMessage = ISNULL(@ErrorMessage + ', ', '')
        + CASE
            WHEN [Action] = 'INSERT' THEN 'Inserted'
            WHEN InsertedId IS NULL THEN 'Deleted'
            ELSE 'Overwrote' 
        END + ' MyId ' + CONVERT(VARCHAR, ISNULL(DeletedId, InsertedId))
    FROM @Result

    RAISERROR('Had to do stuff: %s', 16, 1, @ErrorMessage)
END

Returns

Msg 50000, Level 16, State 1, Line 38
Had to do stuff: Overwrote MyId 1, Inserted MyId 4, Deleted MyId 3, Deleted MyId 2

Note that you have to affect the rows first. But you should really be pre-validating your data anyway if it matters.


My use case here is populating tables that hold a constant set of values, and to throw an error when ad-hoc values are encountered instead of just silently replacing/removing them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.