Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

loop through result set for update

Posted on 2005-03-17
9
Medium Priority
?
1,707 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
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

581 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