Merge help

Database
Enthusiast

Merge help

Hi all,
when I run the below sql it giving error.
could anyone correct me, what is wrong with it.

update emp
from (select b.emp_no emp_no,b.emp_name emp_name, b.emp_code emp_code, b.salary from emp2 a,emp1 b where a.emp_no=b.emp_no) c
set salary=salary*2
where emp_no=c.emp_no
else
insert into emp
(emp_no,emp_name, emp_code, salary)
values
(c.emp_no,c.emp_name,c.emp_code,salary);

*** Failure 5565 The UPDATE specified in the UPSERT statement is a complex update.
Statement# 1, Info =485
*** Total elapsed time was 1 second.

2 REPLIES
Teradata Employee

Re: Merge help

The "UPSERT" form does not allow a FROM clause (joined update).

If using TD12.0, consider MERGE. (If you are not on TD12.0, MERGE is restricted to single-row operations - so appears unlikely to handle what you are trying to do.)

Or do it in two parts, joined UPDATE followed by INSERT ... SELECT ... WHERE NOT EXISTS
Enthusiast

Re: Merge help

Thanks a lot Fred Pluebell.
I have done the same which you told.
Thanks for you help.