asked on

loop through result set for update

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

ASKER CERTIFIED SOLUTION

Hilaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Racim BOUDJAKDJI

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....

Hilaire

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)

KarinLoos

UPDATE pao_data set
bin = p.bin
FROM pao_data d
JOIN per p ON p.auic = d.auic AND d.rbsc = p.rbsc

Hilaire

... 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

Mehul Shah

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

Hilaire

Oops, sorry Karin, same as yours ...

Mehul Shah

Sorry........ by the time I could put in my query.. many of you had already replied to it...

mbart

ASKER

All gave great help but Hilaire got there first. You guys rock!