Home » SQL & PL/SQL » SQL & PL/SQL » Updating two tables in single Update Statement
Updating two tables in single Update Statement [message #267498] Thu, 13 September 2007 09:29 Go to next message
mvnrajan
Messages: 5
Registered: June 2007
Junior Member
Hi Guys,

I had a interview few days back and i got a question as IS IT POSSIBLE TO UPDATE TWO TABLES IN A SINGLE UPDATE STATEMENT ?

Eg:
Update the Empname from EMP and Deptname from DEPT in a single UPDATE Statement Based on some WHERE condition..

As far as i know, its not possible. I searched in Net too, but in vain.

Is it possible????. If So, Any Sample update statement as example would be Appreciable.

Regards
Natarajan.M
Re: Updating two tables in single Update Statement [message #267513 is a reply to message #267498] Thu, 13 September 2007 10:36 Go to previous messageGo to next message
live2learn
Messages: 34
Registered: February 2006
Member
Hi Natarajan,

We can't update two tables in one single SQL statement but we can write a procedure to do so.

Re: Updating two tables in single Update Statement [message #267521 is a reply to message #267513] Thu, 13 September 2007 10:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Simple Answer : No.

Sneaky Answer : No, but one could be tricked into thinking it's possible with a view and instead-of triggers.

http://www.orafaq.com/forum/t/50319/0/

Re: Updating two tables in single Update Statement [message #267807 is a reply to message #267498] Sat, 15 September 2007 01:28 Go to previous messageGo to next message
guarav.sirsa
Messages: 8
Registered: September 2007
Location: Silvassa
Junior Member
Theoritically it is not possible to update two tables from single update statement
But,you can do it eailsy practically.
Eg: - Update EMP set ac_no = 4516544151 where emp_id = 'J 2255";
now this will update single row in one table.
but if u have any DB trigger (row level) written on EMP table.
Many more table can be affected with that. Got my Point.

Regards
Gaurav
9824978892
Re: Updating two tables in single Update Statement [message #267818 is a reply to message #267807] Sat, 15 September 2007 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is really not the same thing to update multiple tables (that you choose in your statement) and putting a trigger on a table that will make things that you don't know or even want.
With a trigger, you have more choice, each time you update the tables, you update multiple tables even if you just want to update this table and no other one.

The best way is a procedure.
You can also use a view and instead of trigger, as Thomas said, designed for a specific case but I don't see the profit compare to a procedure.

Regards
Michel
Re: Updating two tables in single Update Statement [message #267875 is a reply to message #267498] Sat, 15 September 2007 23:34 Go to previous messageGo to next message
nkcoracle
Messages: 5
Registered: June 2006
Location: chennai
Junior Member

Hi,

Its possible to update two tables using single update stmt in mysql, I think it also possible in oracle also.

mysql> update first a, four b set a.no=10, b.id=4 where a.no=9 and b.yn='n';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Rgds,
N K C
Re: Updating two tables in single Update Statement [message #267876 is a reply to message #267498] Sat, 15 September 2007 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Its possible to update two tables using single update stmt in mysql, I think it also possible in oracle also.
If so, post a working example to support your assertion in Oracle compliant SQL along with Oracle version to FOUR decimal places.
Re: Updating two tables in single Update Statement [message #267884 is a reply to message #267875] Sun, 16 September 2007 01:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nkcoracle wrote on Sun, 16 September 2007 06:34
Its possible to update two tables using single update stmt in mysql, I think it also possible in oracle also.

So, after 4 different people told the original poster that this is not possible, you come along and tell us that you think is is possible, with no other foundation than the fact that it is possible in mysql.
Like anacedent says, why not post a working example?
Re: Updating two tables in single Update Statement [message #272708 is a reply to message #267884] Sat, 06 October 2007 08:18 Go to previous messageGo to next message
EswarKishore
Messages: 1
Registered: October 2007
Location: India
Junior Member
Hi Guys,

This is my first mail to this forum.

I have don't idea about Oracle, but we can update information in two tables with a single update statement in MYSQL.

Example Code:-
update hrd_interview_schedule hrd, applicant app set hrd.interviewerId='1010' , hrd.date='10-10-2007' , hrd.time='12:15 AM' , hrd.round='Round1', app.status='2' where hrd.applicantId='1056' and app.applicantId='1056';

check this it will work. I have checked it. Data is getting updated with single update statement.

Cheers,
Kishore Smile

[Updated on: Mon, 08 October 2007 08:01] by Moderator

Report message to a moderator

Re: Updating two tables in single Update Statement [message #272709 is a reply to message #267498] Sat, 06 October 2007 08:42 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I have don't idea about Oracle,
At least you are honest.
>but we can update information in two tables with a single update statement in MYSQL.
This fact does not change the reality that with Oracle one can NOT update two tables in a single SQL Statement.
Stating that it can be done in MYSQL is as useful as stating that on planet EswarKishore the sun rises in the North.
While it may be true, it does not change reality for Oracle.
Previous Topic: Help: Unable to create procedure
Next Topic: Servelet coding in Oracle 8i Vs Oracle 9i
Goto Forum:
  


Current Time: Sat Dec 03 21:55:51 CST 2016

Total time taken to generate the page: 0.09744 seconds