theswally
asked on
T-SQL Loop through recordset and UPDATE based on every record
I'm brand new to T-SQL and need some help. I'm using 2 tables. One is called Receivers, and looks like this:
RECEIVERS
ID, [Account ID], R00
1, 0, 123
2, 0, 456
3, 0, 789
The other is called RecAccountID and looks like this:
RECACCOUNTID
ID, R00
100,123
101,456
102,789
I need to update the Receivers table [Account ID] column with the value from ID in the RecAccountID table, where they are matched on R00. So the Receivers table needs to look like this:
RECEIVERS
ID, [Account ID], R00
1, 100, 123
2, 101, 456
3, 102, 789
Here's what I have so far:
========================== ========== =
declare @accountID int, @recid int, @recr00 varchar(12)
declare rec_cursor CURSOR FOR
select id, r00
from receivers
where [account id] = 999999
OPEN rec_cursor
FETCH NEXT FROM rec_cursor
INTO @recid, @recr00
WHILE @@FETCH_STATUS = 0
BEGIN
declare account_cursor FOR
select ID
FROM RecAccountID
WHERE r00 = @recr00
OPEN account_cursor
FETCH NEXT FROM account_cursor
INTO @accountID
UPDATE Receivers
SET [Account ID] = idfromabove
WHERE ID = @accountID
CLOSE account_cursor
DEALLOCATE account_cursor
FETCH NEXT FROM rec_cursor
INTO @recid, recr00
END
CLOSE rec_cursor
DEALLOCATE rec_cursor
GO
========================== ========== =
It's probably totally wrong. Anyone give me some help on this? Thanks.
Bret
RECEIVERS
ID, [Account ID], R00
1, 0, 123
2, 0, 456
3, 0, 789
The other is called RecAccountID and looks like this:
RECACCOUNTID
ID, R00
100,123
101,456
102,789
I need to update the Receivers table [Account ID] column with the value from ID in the RecAccountID table, where they are matched on R00. So the Receivers table needs to look like this:
RECEIVERS
ID, [Account ID], R00
1, 100, 123
2, 101, 456
3, 102, 789
Here's what I have so far:
==========================
declare @accountID int, @recid int, @recr00 varchar(12)
declare rec_cursor CURSOR FOR
select id, r00
from receivers
where [account id] = 999999
OPEN rec_cursor
FETCH NEXT FROM rec_cursor
INTO @recid, @recr00
WHILE @@FETCH_STATUS = 0
BEGIN
declare account_cursor FOR
select ID
FROM RecAccountID
WHERE r00 = @recr00
OPEN account_cursor
FETCH NEXT FROM account_cursor
INTO @accountID
UPDATE Receivers
SET [Account ID] = idfromabove
WHERE ID = @accountID
CLOSE account_cursor
DEALLOCATE account_cursor
FETCH NEXT FROM rec_cursor
INTO @recid, recr00
END
CLOSE rec_cursor
DEALLOCATE rec_cursor
GO
==========================
It's probably totally wrong. Anyone give me some help on this? Thanks.
Bret
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried that first, ItsTheRide, but that didn't work for me in SQL Server.
Thanks, Sjoerd!
Thanks, Sjoerd!
update RECEIVERS
set [Account_ID] = (select RECACCOUNTID.ID
from RECACCOUNTID
where RECACCOUNTID.R00 = RECEIVERS.R00)