Home » SQL & PL/SQL » SQL & PL/SQL » MERGE STATEMENTS
MERGE STATEMENTS [message #300317] Fri, 15 February 2008 01:09 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,
In Merge statements,instead of table i use the query. Is it possible in Merger statements.

Scripts:
*******

MERGE INTO employees D
USING (select department_id from departments) S
ON (D.department_id=s.department_id)
WHEN MATCHED THEN
UPDATE
SET department_id =s.department_id
WHEN NOT MATCHED THEN
INSERT (department_id)
values(s.department_id);

i executed in these scripts it shows a d.department_id is invalid identifier....but department_id is present in employees table..

how can i resolve it
Re: MERGE STATEMENTS [message #300324 is a reply to message #300317] Fri, 15 February 2008 01:16 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Yes it's possible to use a query in MERGE statement.you can't update the columns used in the join clause.

are you sure you got that error:
Quote:
i executed in these scripts it shows a d.department_id is invalid identifier

cause when i run your sql stat. i got this:
Error report:
SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "D"."DEPARTMENT_ID"
38104. 00000 -  "Columns referenced in the ON Clause cannot be updated: %s"
*Cause:    LHS of UPDATE SET contains the columns referenced in the ON Clause



regards,

[Updated on: Fri, 15 February 2008 01:19]

Report message to a moderator

Re: MERGE STATEMENTS [message #300325 is a reply to message #300317] Fri, 15 February 2008 01:18 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, what could be the use of "SET department_id =s.department_id" when you join on this column and then know that this equality is already true?

Regards
Michel

Previous Topic: Is this a bug or a "feature"
Next Topic: How to identify last column added
Goto Forum:
  


Current Time: Thu Dec 08 16:31:04 CST 2016

Total time taken to generate the page: 0.12550 seconds