Home » SQL & PL/SQL » SQL & PL/SQL » Update two tables using single query
icon5.gif  Update two tables using single query [message #409580] Tue, 23 June 2009 03:31 Go to next message
suman.g
Messages: 88
Registered: June 2009
Member
Hi All,

Can anybody tell that how to update two tables using single query in Oracle. The Primary key of first table is foreign key of second table.

Thanks in advance.

Regards,
Suman


Re: Update two tables using single query [message #409591 is a reply to message #409580] Tue, 23 June 2009 04:12 Go to previous messageGo to next message
tlananthu
Messages: 18
Registered: April 2007
Location: India
Junior Member

Hi,

You can use the following statement:

UPDATE(
select * from table1, table2 where table1.pk=table2.fk)
SET field1 = somevalue, field2 = somevalue
WHERE <some where condition>;

Hope this helps,

Regards, Anantha

[Updated on: Tue, 23 June 2009 05:25] by Moderator

Report message to a moderator

Re: Update two tables using single query [message #409608 is a reply to message #409580] Tue, 23 June 2009 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not possible to update 2 tables with a single SQL statement.

Regards
Michel
Re: Update two tables using single query [message #409622 is a reply to message #409608] Tue, 23 June 2009 06:13 Go to previous messageGo to next message
tlananthu
Messages: 18
Registered: April 2007
Location: India
Junior Member

Sorry for my post. I should have tested it. Thanks for Micheal Cadot for your post pointing out my mistake.
Re: Update two tables using single query [message #409635 is a reply to message #409622] Tue, 23 June 2009 06:41 Go to previous messageGo to next message
suman.g
Messages: 88
Registered: June 2009
Member
Thanks Michel...

Regards,
Suman
Re: Update two tables using single query [message #409636 is a reply to message #409635] Tue, 23 June 2009 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now if you say why you need this maybe we can help you in the actual issue.

Regards
Michel
Re: Update two tables using single query [message #409702 is a reply to message #409636] Tue, 23 June 2009 09:26 Go to previous messageGo to next message
suman.g
Messages: 88
Registered: June 2009
Member
Hi Michel,

Actaully few days back, my friend asked about this.
I checked in Internet but couldn't get correct solution.So, just wanted to know whether it is possible or not.

Thanks a lot Smile

Regards,
Suman

Re: Update two tables using single query [message #409715 is a reply to message #409702] Tue, 23 June 2009 10:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you created a view that displayed the data you wanted to update, you might well be able to create an Instead Of Update trigger on the view, and code the two seperate updates into that trigger.

That would have the effect from the front end of updating both tables from a single statement.
Previous Topic: CREATE TABLE s_dept gets ORA-00900 ERR
Next Topic: Difference between two table structure
Goto Forum:
  


Current Time: Fri Dec 09 11:34:33 CST 2016

Total time taken to generate the page: 0.19639 seconds