I recently wanted to use oracle MERGE INTO to update a table. With MERGE INTO, it usually works with 2 tables, a source and a target table. However, in my case, I only have one table: update the table if the given id exists, otherwise insert a new row. I initially thought the following should work:
MERGE INTO image i
USING (select id, url from offer_image
WHERE id = :id and url = :url) ii
ON (ii.id = i.id AND ii.url = i.url)
WHEN MATCHED THEN
UPDATE SET
title = :title
WHEN NOT MATCHED THEN
INSERT (i.id, i.url, i.title)
VALUES (:id, :url, :title)
However, it only does update and not insert. I consulted oracle DBA and here is his answer:
"Your source always needs to return a non-empty for the merge to work. In this case your source is returning 0 rows."
Then I came up the following merge statement:
MERGE INTO image i
USING (select 1 from dual) ii
ON (i.id = :id AND i.url = :url)
WHEN MATCHED THEN
UPDATE SET
title = :title
WHEN NOT MATCHED THEN
INSERT (i.id, i.url, i.title)
VALUES (:id, :url, :title)
Note that Oracle table DUAL is a special one row one column table. Here is a good explanation about
DUAL
