Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
3 rows affected

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);
ORA-01008: not all variables bound

MERGE INTO fruits dst
USING (
  SELECT 2 AS new_id,
         product,
         active
  FROM   fruits
  WHERE  id = 1
) 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);
2 rows affected

SELECT * FROM fruits ORDER BY id, product


ID
PRODUCT
ACTIVE


1
apple
1

1
orange
1

2
apple
1

2
orange
1