basic MERGE into same table

Question

What I am trying to do is insert/update different versions into a product table. This is a PL/SQL chunk and i have a variable that holds the version number. Lets say the version is 10. Now, I wanna use the merge statement to insert or update versions 11 - 15 for the same product. So, I need to check the table for existing product & version(I have product_id). I need to generate numbers 11 through 15 and check it against the table along with product_id. So, my question is in the using clause - i need to generate the version numbers(11-15) using my temp variable that holds 10 and supplement as my second column(version). Please update the base MERGE statement i have below for my requirement. Thanks.

MERGE INTO product a
USING (SELECT product_id,
              version_id/variable
         FROM product
        WHERE product_id = 1234
      ) b 
      ON (a.product_id = b.product_id AND a.version_id = b.version_id)
 WHEN MATCHED THEN
    [UPDATE product]
 WHEN NOT MATCHED THEN
    [INSERT INTO product]

Answer 1

You can use a row generator, e.g. this will generate version IDs 11 to 15 for your query, so that your merge can insert/update them as required:

MERGE INTO product a
USING (SELECT product.product_id,
              q.version_id
         FROM product
             ,(SELECT ROWNUM+10 version_id FROM dual CONNECT BY LEVEL <= 5) q
        WHERE product_id = 1234
      ) b 
ON (a.product_id = b.product_id AND a.version_id = b.version_id)
WHEN MATCHED THEN
  [UPDATE product]
WHEN NOT MATCHED THEN
  [INSERT INTO product]