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