Reputation: 1118
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
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.
Upvotes: 5