CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle12csql-merge
Adhyatmik
Adhyatmik

Reputation: 1118

Merge Statement for same Table does not insert a new row but updates existing row in Oracle DB

I'm trying to do an upsert operation using MERGE statement on an Oracle 12 DB table called CONFIG whose schema is like below:

id | name | value

If a row with name 'NON_TAXABLE_CODE' exists, update the value to '400', else insert a new row into it.

First Try

MERGE into CONFIG dest USING (SELECT id, name, value from CONFIG where name = 'NON_TAXABLE_CODE') src
    ON (dest.name = src.name)
WHEN MATCHED THEN
    UPDATE SET dest.value = src.value
WHEN NOT MATCHED THEN
    INSERT (id, name, value) VALUES (src.id, src.name, src.value )

This will update the value if the row exists, but does not insert a new row (if no row exists)

SecondTry:

MERGE into CONFIG as dest USING VALUES (0, 'NON_TAXABLE_CODE', '400') as src (id, name, value)
    ON dest.name = src.name
WHEN MATCHED THEN
    UPDATE SET dest.value = src.value
WHEN NOT MATCHED THEN
    INSERT (id, name, value) VALUES (src.id, src.name, src.value )


Error report -
SQL Error: ORA-02012: missing USING keyword
02012. 00000 -  "missing USING keyword"
*Cause:    
*Action:

ThirdTry:

MERGE into CONFIG as dest USING (select 0 as id, 'VERTEX_PRODTYPE_NON_TAXABLE' as name, '400' as value from DUAL) as src
    ON dest.name = src.name
WHEN MATCHED THEN
    UPDATE SET dest.value = src.value
WHEN NOT MATCHED THEN
    INSERT (id, name, value) VALUES (src.id, src.name, src.value )

Error report -
SQL Error: ORA-02012: missing USING keyword
02012. 00000 -  "missing USING keyword"
*Cause:    
*Action:

Could someone please tell me where am I going wrong here ? Thanks.

Upvotes: 1

Views: 783

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31746

The problem is that your select inside the src does not return any row if the where clause is not satisfied. Thus, it will neither do an update nor an insert when the condition fails.

To avoid this you can select the values from the dual table.

MERGE INTO config dest USING (
     SELECT 100 AS id,
            'NON_TAXABLE_CODE' AS name,
            400 AS value
     FROM dual
)
src ON ( dest.name = src.name )
WHEN MATCHED THEN UPDATE SET dest.value = src.value
WHEN NOT MATCHED THEN INSERT (
     id,
     name,
     value ) VALUES (
     src.id,
     src.name,
     src.value );

This will update the row when the name matches but inserts the hardcoded values selected from the dual when not matched.

DB Fiddle Demo

Upvotes: 5

Related Questions