ORA-00928: missing SELECT keyword on merge

Question

I am trying to use merge and I can see the following error

ORA-00928: missing SELECT keyword

  merge into table using(select * from table on val = val1)
  when matched then
  (update set
     val2 = val3 where val = val1)
  when not matched then
  (Insert query)

What did I miss?

Answer 1

The syntax is quite prescriptive. The matching criteria belong in a separate ON clause, and must be in brackets. Find out more.

SQL> merge into t23 tgt
  2  using ( select * from t23 ) q
  3  on (tgt.col1 = q.col1)
  4  when not matched then
  5      insert values (q.col1, q.id+20, q.col2, q.date_time, q.qty, q.dt)
  6  when matched then
  7      update set tgt.col2 = q.col2 * 2
  8  /

4 rows merged.

SQL> 

"I am using the same table everywhere,could that be the issue? "

Nope. As my example query shows, MERGE can run with the same table as the source and target. However, the question is why would you want to do that? Either all the rows match, in which case you're doing an update, or none match, which is an insert ... select from.

---

Having looked at your pseudo query again, I think I can see what you'retrying to do. The trick is to make sure that the criteria in the ON clause match one row in the target set to one row in the USING set. Otherwise it will hurl ORA-30926: unable to get a stable set of rows in the source tables

So here's a re-worked version of my previous query.

Starting data set (the USING set):

SQL> select id, col1, qty, col2 from t23;

ID                                   COL1        QTY C
------------------------------ ---------- ---------- -
ABCD0001                                5        100 N
ABCD0002                               10         10 N
ABCD0003                               15         20 N
ABCD0004                               20        -30 N
ABCD0005                               35         20 N
ABCD0006                               25        100 N
ABCD0007                               30         30 N
ABCD0008                               40        -30 N

8 rows selected.

SQL>

The ON criteria join on two columns to guarantee a stable set:

SQL> merge into t23 tgt
  2  using ( select * from t23 ) q
  3  on (tgt.id = q.id 
  4       and tgt.col1 = tgt.qty)
  5  when not matched then
  6      insert values (q.id, q.col1+20, 'X', q.date_time, q.qty, q.dt)
  7  when matched then
  8      update set tgt.col2 = 'Y'
  9  /

8 rows merged.

SQL>

The outcome is two updates and six inserts...

SQL> select id, col1, qty, col2 from t23;

ID                                   COL1        QTY C
------------------------------ ---------- ---------- -
ABCD0001                                5        100 N
ABCD0002                               10         10 Y
ABCD0003                               15         20 N
ABCD0004                               20        -30 N
ABCD0005                               35         20 N
ABCD0006                               25        100 N
ABCD0007                               30         30 Y
ABCD0008                               40        -30 N
ABCD0008                               60        -30 X
ABCD0004                               40        -30 X
ABCD0001                               25        100 X
ABCD0003                               35         20 X
ABCD0006                               45        100 X
ABCD0005                               55         20 X

14 rows selected.

SQL> 

The obvious danger with this tactic is primary key collisions. My table is a bagatelle and doesn't have a primary key. In real life your table should have a primary key, and you will need to handle that in the INSERT clause.

---

Interestingly, the WHERE clause in the UPDATE branch is legal syntax (although it's redundant in the OP's example, as it duplicates the ON criteria). Running this MERGE against the original data set merges six rows (six inserts, no updates):

merge into t23 tgt
using ( select * from t23 ) q
on (tgt.id = q.id 
     and tgt.col1 = tgt.qty)
when not matched then
    insert values (q.id, q.col1+20, 'X', q.date_time, q.qty, q.dt)
when matched then
    update set tgt.col2 = 'Y'
    where tgt.col2 = 'Z'
/

The WHERE clause only applies in the MATCHED branch, and is used to determine whether matched rows get updated or deleted.

Answer 2

The join condition is wrong:

merge into table using (
   select * from table 
) on val = val1 --<< needs to go outside of the sub-select 
when matched then
(update set
   val2 = val3 where val = val1)
when not matched then
(Insert query)