updating records with values from a second table 2

[sapatos]

Hi,

I have two tables (A & B), From table A I need to take the values from a few fields from each record and use these values to look up a record on table B. With The matching record on table B I take the values from a few fields and use these values to update some fields on table A. I'm doing this from pl/sql with cursors but its taking ages, not sure how to do this in a simple update statment with joins. When I write a select statment using the join it returns in seconds but can't find a way to include this in an update. e.g.

update table A a, B b set a.col1 = b.col1,
a.col2 = b.col2
where a.col3 = b.col3
and a.col4 = b.col4

that code is rubbish but it might give you an idea what I'm trying to accomplish. Clear as Mud???

Thanks for the help

 

[Dagon]

update tableB b set b.col1 =
(select a.col1
from tableA a
where a.col2 = b.col2
and a.col3 = b.col3
and a.col4 = b.col4)

 

[Dagon]

Might also want to do this:

update tableB b set b.col1 =
(select a.col1
from tableA a
where a.col2 = b.col2 
and a.col3 = b.col3
and a.col4 = b.col4)
where exists
(select 1
from tableA a
where a.col2 = b.col2 
and a.col3 = b.col3
and a.col4 = b.col4)

Otherwise, any non-matching rows will get updated to NULL.

 

[sapatos]

thanks worked a treat

 

[SantaMufasa]

Sapatos,

Dagon always produces and posts excellent solutions. Each solution takes a non-trivial amount of time to create. He certainly deserves our continued thanks.

The "currency" that we use to both offer thanks here on Tek-Tips, and to signal to subsequent readers that a solution "works a treat" for us, are

s. We award

s by clicking on the link "Thank Dagon for this valuable post!".

I noticed that during your two years here on Tek-Tips.com, you have not yet awarded any

s as a result of any of the 17 threads you have started here. So, my presumption is that you were not aware of the

feature or how to use it.

So, when you wish to show your gratitude on Tek-Tips.com, we invite you to click the link that generates the well-deserved

.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

I thought that I would give my previous reply an overnight chance for you to reply, Sapatos. At this point, I have some constructive observations, that I hope you will take in the spirit of friendship and co-operation with which I make the observations:[ul][li]Your participation here on Tek-Tips appears to be what I call, Drive-By Threading...Since you joined Tek-Tips 27 months ago, you have started 17 threads in 8 forums, yet you have posted only 2 responses in your own threads, and never responded to anyone else's thread.[/li][li]You have never awarded anyone a

for their efforts[/li][li]You have never attempted to help anyone else with a reply to their threads.[/li][/ul]Since we are a community, Sapatos, we need participation from our community members that represents good citizenship. There is probably still time for you to go back to your previous threads and express

appreciation where appreciation is due, and to move forward as a responsible participant. Otherwise, Tek-Tips management tends to restrict access.


To help get the ball rolling for you, I'm clicking the Thank Dagon... link, in your behalf, for his excellent post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sapatos]

hi santamufasa,

I apologise for my lack of thanks there. I wasn't aware we had to click on those stars. My bad! I missed your last post as I've not been on email since last week. I'll make sure it doesn't happen again.

Regards,

 

[SantaMufasa]

Sapatos,

Repentence is good for the soul. <grin>

Thanks for checking in with us. We look forward to being of assistance to you in the future.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ChrisHunt]

It's also worth pointing out that you can do this:

update tableB b
set (b.col1, b.col2, b.col3) =
(select a.col1, a.col2, a.col3
from tableA a
where a.col2 = b.col2 
and a.col3 = b.col3
and a.col4 = b.col4)
where exists
(select 1
from tableA a
where a.col2 = b.col2 
and a.col3 = b.col3
and a.col4 = b.col4)

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd