Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE MULTIPLE TABLES IN A SINGLE QUERY (oracle 10g)
UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #540997] Fri, 27 January 2012 00:53 Go to next message
nvsr
Messages: 51
Registered: December 2011
Location: HYDERABAD
Member

Hi All
Actully i am updating two table of data.. but below error message came..

update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #540999 is a reply to message #540997] Fri, 27 January 2012 01:02 Go to previous messageGo to next message
a_oracle
Messages: 95
Registered: November 2010
Member
You can't update two tables in one query
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541000 is a reply to message #540999] Fri, 27 January 2012 01:15 Go to previous messageGo to next message
nvsr
Messages: 51
Registered: December 2011
Location: HYDERABAD
Member

can you tel me How to update that Query?
Please ....
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541001 is a reply to message #541000] Fri, 27 January 2012 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59083
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean?

Regards
Michel
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541002 is a reply to message #541001] Fri, 27 January 2012 01:22 Go to previous messageGo to next message
nvsr
Messages: 51
Registered: December 2011
Location: HYDERABAD
Member

update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view


when i am updaintg this query ... This Error msg Happing...
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541005 is a reply to message #540999] Fri, 27 January 2012 01:37 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
a_oracle wrote on Fri, 27 January 2012 07:02
You can't update two tables in one query




What he said.
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541007 is a reply to message #541002] Fri, 27 January 2012 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59083
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
a_oracle wrote on Fri, 27 January 2012 08:02
You can't update two tables in one query


What is not clear in this post?

Regards
Michel
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541009 is a reply to message #540997] Fri, 27 January 2012 01:41 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
The error you faced is described e.g. here: http://ora-01776.ora-code.com/
Quote:
ORA-01776: cannot modify more than one base table through a join view
Cause: Columns belonging to more than one underlying table were either inserted into or updated.
Action: Phrase the statement as two or more separate statements.

Maybe you should accept the reality instead of fighting against it.
update ( <your query in UPDATE> )
set dname = 'Y';
update emp e
set ename = 'X'
where empno = 7788;


[Edit: corrected second UPDATE statement - it would probably cause ORA-01779: "cannot modify a column which maps to a non key-preserved table"; However I am quite lazy to check it]

[Updated on: Fri, 27 January 2012 01:46]

Report message to a moderator

Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541029 is a reply to message #541009] Fri, 27 January 2012 02:40 Go to previous messageGo to next message
nvsr
Messages: 51
Registered: December 2011
Location: HYDERABAD
Member

i want to change the two tables at a time in single query. i.e ename and dname

update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno)
set ename = 'X', dname = 'Y'
where deptno = '40'
Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541031 is a reply to message #541029] Fri, 27 January 2012 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59083
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
flyboy wrote on Fri, 27 January 2012 08:41
...Maybe you should accept the reality instead of fighting against it.

Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY [message #541068 is a reply to message #541029] Fri, 27 January 2012 08:06 Go to previous message
joy_division
Messages: 4512
Registered: February 2005
Location: East Coast USA
Senior Member
nvsubbareddy wrote on Fri, 27 January 2012 03:40
i want to change the two tables at a time in single query. i.e ename and dname

update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno)
set ename = 'X', dname = 'Y'
where deptno = '40'


Boy, are you dense, or blind. Maybe should get a screen reader.
Previous Topic: Right way to store IPV4 and MAC address...???
Next Topic: Need to substitute the column name from a variable for the Select query
Goto Forum:
  


Current Time: Tue Sep 16 09:05:42 CDT 2014

Total time taken to generate the page: 0.33053 seconds