Help with using Oracle SQL MERGE statement

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.

Help with using Oracle SQL MERGE statement

First of all, it is always a good idea to post Oracle version. I can't reproduce the error:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

SQL> select * from wn_termlist;

TERMID GROUPID
---------- --------------------
1 ALL TERMINALS
2 SOME TERMINAL

2 rows selected.

SQL> select * from sa_term;

TERMID
----------
1
2
3

3 rows selected.

SQL> MERGE INTO wn_termlist wn
2 USING sa_term sa
3 ON (sa.termid = wn.termid
4 AND wn.groupid = 'ALL TERMINALS')
5 WHEN NOT MATCHED THEN
6 INSERT (wn.groupid,wn.termid)
7 VALUES ('ALL TERMINALS',sa.termid)
8 /

2 rows merged.

SQL> select * from wn_termlist;

TERMID GROUPID
---------- --------------------
1 ALL TERMINALS
2 SOME TERMINAL
3 ALL TERMINALS
2 ALL TERMINALS

4 rows selected.

SQL>

Please post SQL*Plus snippet showing code execution and associated errors.