Home » SQL & PL/SQL » SQL & PL/SQL » Update using join of tables (Oracle 10g)
Update using join of tables [message #326487] Wed, 11 June 2008 12:51 Go to next message
tejas_gandhi
Messages: 8
Registered: August 2007
Junior Member
A simple query, but stucked so asking here.

I have 2 tables with structure and data as follows.
SQL> select * from temp1;
ASTRING | ANUMBER |DATE1
----------|-------------|---------
A | 1 |
B | 2 |
C | 3 |
D | 4 |

select * from temp2;
ASTR | ANUM
----------|----------
A | 2
B | 2
C | 3

now i have to SET temp1.date1=SYSDATE where temp1.astring=temp2.astr and temp1.anumber=temp2.num

How to write this in update query? Sad

Thanks
Re: Update using join of tables [message #326488 is a reply to message #326487] Wed, 11 June 2008 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use EXISTS in where clause of UPDATE.

Regards
Michel
Re: Update using join of tables [message #326489 is a reply to message #326488] Wed, 11 June 2008 12:59 Go to previous messageGo to next message
tejas_gandhi
Messages: 8
Registered: August 2007
Junior Member
Can u please write update query here. How to use EXISTS for joining 2 columns??
Re: Update using join of tables [message #326490 is a reply to message #326487] Wed, 11 June 2008 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
We DON'T do homework for students.
You need to learn on your own.
Re: Update using join of tables [message #326522 is a reply to message #326487] Wed, 11 June 2008 21:35 Go to previous messageGo to next message
tejas_gandhi
Messages: 8
Registered: August 2007
Junior Member
Chill man.. I am not student and the query was not actually reqd for tables i shown in example. Just my day was bad, so i was stucked there Sad

Anyway the solution is
update temp1 set date1=sysdate
where exists(select astr from temp2 where temp1.astring=temp2.astr and temp1.anumber=temp2.anum);

Re: Update using join of tables [message #326574 is a reply to message #326522] Thu, 12 June 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for posting the solution you found.

Regards
Michel
Re: Update using join of tables [message #326585 is a reply to message #326487] Thu, 12 June 2008 01:17 Go to previous message
tejas_gandhi
Messages: 8
Registered: August 2007
Junior Member
Thanks to you Michel! Your pointer helped me to found solution! Smile
Previous Topic: UPDATE vs DELETE...INSERT
Next Topic: SQLs (completed & currently running ) in a session
Goto Forum:
  


Current Time: Sun Dec 11 02:26:32 CST 2016

Total time taken to generate the page: 0.09552 seconds