CODEWITHSUNDEEP
sqloracle-database
Girish R Acharya
Girish R Acharya

Reputation: 140

merge error (not able to insert the values)

Friends i am using merge syntax to update and insert values in a table 

MERGE INTO iq_dnr b
   USING (SELECT *
            FROM iq_dnr
           WHERE co_series = 'IO' AND CATEGORY = 9 AND dnr_from = 13567362) a
   ON (    a.co_series = b.co_series
       AND a.CATEGORY = b.CATEGORY
       AND a.dnr_from = b.dnr_from)
   WHEN MATCHED THEN
      UPDATE
         SET b.dnr_to = 0001
   WHEN NOT MATCHED THEN
      INSERT (co_series, CATEGORY, dnr_from, dnr_to)
      VALUES ('IO', 9, 13567362, 00001)

and i want to ask whether it is possible to check and insert in one table only like i did in above query. know the problem is i am able to successfully update the table but i am not able to insert the values . plss help me

Upvotes: 1

Views: 140

Answers (1)

Akash KC
Akash KC

Reputation: 16310

Try with using DUAL, it might solve your problem:

MERGE INTO iq_dnr b
   USING (SELECT 'IO' co_series,
                  9 CATEGORY,
                  13567362 dnr_from
            FROM dual) a
   ON (    a.co_series = b.co_series
       AND a.CATEGORY = b.CATEGORY
       AND a.dnr_from = b.dnr_from)
   WHEN MATCHED THEN
      UPDATE
         SET b.dnr_to = 0001
   WHEN NOT MATCHED THEN
      INSERT (co_series, CATEGORY, dnr_from, dnr_to)
      VALUES ('IO', 9, 13567362, 00001)

Using above DUAL ensures that it returns something anyway which keeps MERGE statement working....

Upvotes: 3

Related Questions