?
Solved

loop through result set for update

Posted on 2005-03-17
9
Medium Priority
?
1,705 Views
Last Modified: 2011-10-03
I have a query which returns a group of records and I want to use the returned values in an update function.  For example:
update pao_data set bin =  
(select p.bin, d.auic, d.rbsc  from per p inner join pao_data d on p.auic = d.auic and p.rbsc = d.rbsc)
where auic = d.auic and rbsc = d.rbsc
 
Need quick help as usual
0
Comment
Question by:mbart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 13564624
You can join your select statement as if it were a table, just provide an alias
as follows

update A
set bin =  B.bin
from pao_data A
Inner Join (
     select p.bin, d.auic, d.rbsc  
     from per p
     inner join pao_data d on p.auic = d.auic and p.rbsc = d.rbsc
) B ON A.auic = B.auic and A.rbsc = B.rbsc
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 13564631
update pao_data set p.bin = <your_value> where p.bin in(
(select p.bin, d.auic, d.rbsc  from per p inner join pao_data d on p.auic = d.auic and p.rbsc = d.rbsc
where auic = d.auic and rbsc = d.rbsc))

Hope this helps....
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13564661
However a simpler version, using a correlated subquery, would be

update A
set bin =  (select bin from per where auic = A.auic and rbsc = A.rbsc)
from pao_data A
where exists (select 1 from  per where auic = A.auic and rbsc = A.rbsc)

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:KarinLoos
ID: 13564665
UPDATE pao_data set
   bin  = p.bin
FROM pao_data     d
JOIN per          p ON p.auic = d.auic AND d.rbsc = p.rbsc
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13564671
... or

update A
set bin = B.bin
from pao_data A
inner join per B on B.auic = A.auic and B.rbsc = A.rbsc
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13564679
try the below query

update pao_data set bin = per.bin
from pao_data,per
where per.auic = pao_data.auic and per.rbsc = pao_date.rbsc
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13564682
Oops, sorry Karin, same as yours ...
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13564715
Sorry........ by the time I could put in my query.. many of you had already replied to it...
0
 

Author Comment

by:mbart
ID: 13564760
All gave great help but Hilaire got there first.  You guys rock!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question