mercredi 29 juin 2016

Oracle MERGE - if not matched then update if condition passes

I need to merge some values into a table, updating a field when a row with the specified key already exists, or inserting a new row if it doesn't exist.

This is my table:

profiles(name, surname, active);

where:

name    VARCHAR2(30)
surname VARCHAR2(30)
active  NUMBER(1)

name and surname -> composite primary key

I'm using this query:

MERGE INTO profiles USING (
    SELECT
        'Mark' myName,
        'Zibi' mySurname,
        '1'    myActive
   FROM DUAL
) ON (
   name = myName
   AND surname = mySurname
)
WHEN MATCHED THEN
    UPDATE SET
        active = myActive
WHEN NOT MATCHED THEN
    INSERT (
        name,
        surname,
        active
    ) VALUES (
        myName,
        mySurname,
        myActive
    );

It works, but it updates a record even if active is already set to 1.

What I would like to do is something like this:

WHEN MATCHED THEN
    IF(active != myActive)
        UPDATE SET
            active = myActive
    ELSE
        RAISE CUSTOM EXCEPTION
WHEN NOT MATCHED THEN
    INSERT [...]

Is that possible? AFAIK I cannot put an if like this into a MERGE statement, so how could it be done?

Aucun commentaire:

Enregistrer un commentaire