I need to execute the following MySQL-query in Oracle:
INSERT INTO users VALUES(1,10) ON DUPLICATE KEY UPDATE points = 10;
Is there something else besides merge? I just don't understand it.
Add a comment
|
3 Answers
You would need to use a MERGE. Something like
MERGE INTO users dest
USING( SELECT 1 user_id, 10 points FROM dual) src
ON( dest.user_id = src.user_id )
WHEN MATCHED THEN
UPDATE SET points = src.points
WHEN NOT MATCHED THEN
INSERT( user_id, points )
VALUES( src.user_id, src.points );
1 Comment
user2602807
Be careful using UPDATE for counter increment! you have to use aliases for both src and dst, UPDATE SET COUNTER = dst.COUNTER + src.COUNTER, otherwise it will take src value by default! UPDATE SET COUNTER = COUNTER + src.COUNTER (uses src.COUNTER by default for left argument)
If you don't want to use MERGE, you can try:
begin
INSERT INTO users VALUES(1,10);
exception
when dup_val_on_index then
update users
set points = 10
where id = 1;
end;
Comments
MERGE INTO users u
USING (SELECT 1 as id FROM dual) a
ON a.id = u.id
WHEN MATCHED THEN UPDATE SET u.points = 10
WHEN NOT MATCHED THEN INSERT (id, points) VALUES (1, 10);
