Help with using Oracle SQL MERGE statement

by Michael Walker
(London)

Can you please provide advice on the following: I am trying to update the table wn_termlist so that it contains all termid's from the table sa_term against the groupid 'ALL TERMINALS'.

wn_termlist sa_term
termid termid
groupid

The primary key is termid.

MERGE INTO wn_termlist wn
USING sa_term sa
ON (sa.termid = wn.termid
AND wn.groupid = 'ALL TERMINALS')
WHEN NOT MATCHED THEN
INSERT (wn.groupid,wn.terminalid)
VALUES ('ALL TERMINALS',sa.terminalid);

When I try to run this I get an error at line 7: ORA-00905: missing keyword. This is my first attempt at using merge and I have no reference material for it.

To just insert new records into a table the Oracle MERGE statement is the wrong statement. A better approach would be to use an insert statement with a correlated sub-query. Something along these lines:-

insert into wn_termlist select * from sa_term where not exists
(select 1 from sa_term where sa_term.termid=wn_termlist.termid)

On the other hand if you want to both update existing records and insert new ones then using the MERGE statement is the right approach and all that is needed is an update statement and the WHEN MATCHED clause.

eg.
MERGE INTO wn_termlist wn
USING sa_term sa
ON (sa.termid = wn.termid
AND wn.groupid = 'ALL TERMINALS')
WHEN MATCHED THEN
UPDATE SET wn.terminalid = sa.terminalid
WHEN NOT MATCHED THEN
INSERT (wn.groupid,wn.terminalid)
VALUES ('ALL TERMINALS',sa.terminalid);

As the name suggests the Oracle MERGE statement will add new records to the table and update existing rows in one operation. To achieve this you need to tell Oracle the match conditions plus which columns to update when there is a match between an existing row and a row in the update set and which columns to insert into a new row.