CODEWITHSUNDEEP
sqloracle11g
Phate
Phate

Reputation: 6612

Oracle merge from another table gives me error ORA-00904 invalid identifier

I have a table T1 containing data I have to either insert or update in another table T2. So I came up with the following merge:

merge into Table1 T1 using (select a, max(b) b from Table2 group by a order by a) T2
on (T1.a=T2.a)
when matched
then
 update set b=T2.b,last_update=sysdate
when not matched
then
insert (a,b,last_update) values (T2.a,T2.b,sysdate)

As,at start, T1 is empty merge goes into insert statement which is fine, but I get:

ORA-00904: "T2"."a": invalid identifier

How come?

Upvotes: 0

Views: 437

Answers (1)

Rahul
Rahul

Reputation: 77876

I don't see anything wrong except that the column names are not qualified with table aliases properly. Try like below once

merge into Table1 T1 using (select a, max(b) b from Table2 group by a order by a) T2
on (T1.a = T2.a)
when matched then
 update set T1.b = T2.b, T1.last_update = sysdate
when not matched then
insert (T1.a,T1.b,T1.last_update) values (T2.a,T2.b,sysdate)

Upvotes: 1

Related Questions