ora-00904 and merge

[Aljaz]

Hi,

I am getting an Ora-00904 error ("TEST"."ENA": invalid identifier) when I try to issue this merge statement

merge into test
using (select ena from test1) t1
on (t1.ena = test.ena)
when matched then update set test.ena = t1.ena
when not matched then insert (test.ena) values (t1.ena)

if I issue this statement then I don't get the error and I don't get the right result as well

merge into test
using (select ena from test1) t1
on (t1.ena = 1)
when matched then update set test.ena = t1.ena
when not matched then insert (test.ena) values (t1.ena)


Here are structures and content of tables test and test1
desc test
Name Null? Type
------------ -------- ------------
ENA VARCHAR2(10)


desc test1
Name Null? Type
------------ -------- ------------
ENA VARCHAR2(10)


select * from test;
ENA
----------
1
2
3
4
5

select * from test1;
ENA
----------
3
4
5
6
7
8

[chellam]

try it out!

--why dont u try giving alias name for table test as

merge into test t
using (select ena from test1) t1
on (t1.ena = t.ena)
when matched then update set t.ena = t1.ena
when not matched then insert values (t1.ena)

[Aljaz]

Now I am getting

ERROR at line 3:
ORA-00904: "T"."ENA": invalid identifier

OK what is the difference between this two statements??

merge into test t1
using (select ena from test1) t2
on (t1.ena = t2.ena)
when matched then update set t1.ena = t2.ena
when not matched then insert (t1.ena) values (t2.ena)

and

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);

Well the second one works. From what I can see the syntax is OK in the first query so the problem is somewhere else.

I would realy like to know where the problem is, becouse I just can't figure it out.

[TimHall]

Hi.

It's pretty simple when you know know what it is.

You cannot put a column that is in the ON clause into the UPDATE statement. This means that this will fail:

merge into tab1 t1
using (select * from tab1) t2
on (t1.id = t2.id)
when matched then
update set t1.id = t2.id, -- Dodgy line
t1.description = t2.description
when not matched then
insert (id, description)
values (t2.id, t2.description);

but this will work:

merge into tab1 t1
using (select * from tab1) t2
on (t1.id = t2.id)
when matched then
update set t1.description = t2.description
when not matched then
insert (id, description)
values (t2.id, t2.description);

The impact of this is that there are some situations where you just can't use a merge. Namely when you need to update a column that you are using to identify the record in the ON clause.

Cheers

Tim...

[Aljaz]

I can see clearly now the rain is gone!

Thank you