Let's say I have a table named FRUITS like this:
ID | PRODUCT | ACTIVE |
---|---|---|
1 | apple | 1 |
1 | orange | 1 |
2 | orange | 0 |
(ID = 1 has two PRODUCTS, and both have ACTIVE = 1).
Now I want output in same table like this:
ID | PRODUCT | ACTIVE |
---|---|---|
1 | apple | 1 |
1 | orange | 1 |
2 | orange | 1 |
2 | apple | 1 |
So what I need is to Insert or Update values in a table, based on a condition for PRODUCT and ID from same table.
Or in another words - I want to copy all columns from existing ID into rows of given ID with same PRODUCT (UPDATE) or INSERT if rows with same PRODUCT doesn't exist under given ID.
Is there a way that this could be done with a single statement, like MERGE?
I tried with this MERGE statement, but It doesn't work:
MERGE INTO fruits d
USING (SELECT id,
product,
active
FROM fruits
WHERE id = :old_id_in) s
ON (d.id = :new_id_in AND d.product = s.product)
WHEN MATCHED THEN
UPDATE SET d.product = s.product,
d.active = s.active
WHERE d.id = :new_id_in
WHEN NOT MATCHED THEN
INSERT (d.id, d.product, d.active)
VALUES(:new_id_in, s.product, s.active);
Any help appreciated !
You can use:
MERGE INTO fruits dst
USING (
SELECT :new_id_in AS new_id,
product,
active
FROM fruits
WHERE id = :old_id_in
) src
ON (dst.id = src.new_id AND dst.product = src.product)
WHEN MATCHED THEN
UPDATE
SET active = src.active
WHEN NOT MATCHED THEN
INSERT (id, product, active)
VALUES(src.new_id, src.product, src.active);
Which, for the sample data:
CREATE TABLE fruits (ID, PRODUCT, ACTIVE) AS
SELECT 1, 'apple', 1 FROM DUAL UNION ALL
SELECT 1, 'orange', 1 FROM DUAL UNION ALL
SELECT 2, 'orange', 0 FROM DUAL;
Then, after the MERGE
the table contains:
SELECT * FROM fruits ORDER BY id, product
ID | PRODUCT | ACTIVE |
---|---|---|
1 | apple | 1 |
1 | orange | 1 |
2 | apple | 1 |
2 | orange | 1 |