Update or Insert into table, using rows from same table

Question

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 !

Answer 1

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

fiddle