Home » SQL & PL/SQL » SQL & PL/SQL » Problem ... Update Query with join two tables
Problem ... Update Query with join two tables [message #204145] Sat, 18 November 2006 03:29 Go to next message
bhadresh4u
Messages: 41
Registered: May 2005
Location: Japan
Member
Hi..!

I have a two Tables, named Tab1 & Tab2.
One common column is Key_col.

I have to update Tab1.col1 using the Tab2.col1 which flag is Yes.

I can do this using the Merge statement but how is posible using the Single UPDATE statement. How to Join this two tables in Update statement.



Tab1				Tab2		

Key_Col	Col1			Key_Col	Col1	Flag
A001				A001	1000	N
A002	2000			A002	2000	Y
A003				A003	3000	N
A004				



I try this one but its update all rows of Tab1 using Null where row not found in tab2.

Update tab1 a
set a.col1 = (Select b.col1 from tab2 b
where a.key_col = b.key_col
and b.flag = 'Y');


Update tab1 a 
set a.col1 = (Select b.col1 from tab2 b 
               where a.key_col = b.key_col
                 and b.flag = 'Y');


Please help me.

Thanks in advance..

Bhadresh


Re: Problem ... Update Query with join two tables [message #204147 is a reply to message #204145] Sat, 18 November 2006 03:55 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
so handle the null using to_char(nvl) command
Re: Problem ... Update Query with join two tables [message #204151 is a reply to message #204147] Sat, 18 November 2006 04:04 Go to previous messageGo to next message
bhadresh4u
Messages: 41
Registered: May 2005
Location: Japan
Member

Tab1 is my Master Table & Tab2 is my transction table.

Tab2 contains only periodic data. and I update Master table periodically. Tab1.col1 had a lots of entries which is done in previous periods so I dont wants to lost thats data. & update only which is in current period i.e. found in tab2 based on Tab2.flag. I identified it by tab2.flag column and update only those values.

So I am not able to use NVL or to_char.

any other solution in ur mind then share with me please.


Re: Problem ... Update Query with join two tables [message #204199 is a reply to message #204151] Sat, 18 November 2006 17:58 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Update tab1 a 
set a.col1 = (Select b.col1 from tab2 b 
               where a.key_col = b.key_col
                 and b.flag = 'Y')
where key_col IN (
    select key_col from tab2)


or - if you want it to run fast -
update (
   select a.col1 as old_col1
   ,      b.col1 as new_col1
   from tab1 a
   join tab2 b using (key_col)
)
set old_col1 = new_col1

but you will need a unique/pk on tab2.keycol, otherwise Oracle will raise a Key Preserved Update error.

Ross Leishman
Previous Topic: error message
Next Topic: using partition name with date function to_char(sysdate,'ddmmyy')
Goto Forum:
  


Current Time: Tue Dec 06 10:18:19 CST 2016

Total time taken to generate the page: 0.08836 seconds