-
ora-00904 and merge
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
-
try!!!!!!
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)
let us help eachother
-
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.
-
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...
-
I can see clearly now the rain is gone!
Thank you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|