SQL ORACLE ORA-00969: missing ON keyword?

Question

I have two different tables : Table_a and Table_b, both have same columns PartNo and Material_Desc. I want the Material_Desc in Table_b to update the Material_Desc in Table_a when PartNo are equals. This is my query

MERGE INTO Table_b 
USING ( SELECT t1.rowid AS rid
               , t2.Material_Desc
        FROM Table_b t1 
           JOIN Table_a t2 
           ON Table_b.PartNo = Table_a.PartNo ) 
ON rowid = rid 
WHEN MATCHED THEN 
    UPDATE 
    SET Table_a.Material_Desc = Table_b.Material_Desc;

I know Oracle doesn't support joins for update statement so I tried the above query. Still it gives me ORACLE ORA-00969: missing ON keyword error

See my comment on this answer: http://stackoverflow.com/a/2446859/244826 — Sonny, Aug 05 '12 at 20:44
The condition after the `ON ` **must** be in brackets: `ON (rowid = rid)` — , Apr 18 '13 at 17:08

score 10 · Accepted Answer · answered Apr 18 '13 at 15:52

10

You might put "(" and ")". So write :

ON ( rowid = rid )

answered Apr 18 '13 at 15:52

rudrirk

116
1
2

score 2 · Answer 2 · answered Sep 28 '11 at 01:53

2

First of all, the syntax problem: when you merge using a subquery, you have to alias it to something. But more importantly, you don't need to use a subquery.

MERGE INTO Table_a USING Table_b ON (Table_a.PartNo = Table_b.PartNo) 
WHEN MATCHED THEN UPDATE SET Table_a.Material_Desc = Table_b.Material_Desc

First of all, you have to put the table you want to update into the INTO portion. Second, doing the join in a subselect and merging on rowids won't offer you any benefit that I can think of.

answered Sep 28 '11 at 01:53

Dan

10,990
7
51
80

am getting ORA-30926: unable to get a stable set of rows in the source tables error for the above query – sailaja Sep 28 '11 at 02:19
Maybe this helps to understand ORA-30926: https://forums.oracle.com/forums/thread.jspa?threadID=257751 see the last comment. – Sep 28 '11 at 02:39
sorry I didn't get it from the last comment of the above link.Please help me out. – sailaja Sep 28 '11 at 03:49

schurik · Answer 3 · 2011-09-28T09:27:11.150

0

perhaps not as efficient as a merge statment, but should do the work:

update table_b tb
set tb.Material_Desc = (select ta.Material_Desc 
                        from   table_a ta 
                        where  ta.PartNo = tb.PartNo
                        and    rownum = 1
                       )
where tb.rowid in (
                  SELECT t1.rowid                       
                    FROM Table_b t1 
                        , Table_a t2 
                    WHERE  t1.PartNo = t2.PartNo
                   )

edited Sep 28 '11 at 09:27

answered Sep 28 '11 at 08:58

schurik

7,798
2
23
29

This query gives ORA-01427: single-row subquery returns more than one row error. – sailaja Sep 28 '11 at 09:09
@sailaja: I have assumed that partno is unique. I've adjusted my answer, so only the first found entry is taken – schurik Sep 28 '11 at 09:26
No,each Part_No has multiple values like for ex.001A has 100 items and 001B has 50 items with unique Serial_No, but the Description is same, so I have another table(table_a) which holds only PartNo & Material_Desc, I want to update all the desc for these PartNo's from this table_a – sailaja Sep 28 '11 at 09:37

SQL ORACLE ORA-00969: missing ON keyword?

3 Answers3