All Forums Database
Sunar 59 posts Joined 02/08
12 May 2008
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.

Fred 1096 posts Joined 08/04
12 May 2008

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

Sunar 59 posts Joined 02/08
13 May 2008

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

You must sign in to leave a comment.