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
theswallyAsked:
Who is Participating?
 
SjoerdVerweijConnect With a Mentor Commented:
This should do it:

Update Receivers Set [Account ID] = ID From Receivers Inner Join RECACCOUNTID On RECACCOUNTID.R00 = Receivers.R00
0
 
itstherideSystems AdministrationCommented:
This should work also.

update RECEIVERS
set [Account_ID] = (select RECACCOUNTID.ID
                             from RECACCOUNTID
                             where RECACCOUNTID.R00 = RECEIVERS.R00)
0
 
theswallyAuthor Commented:
I tried that first, ItsTheRide, but that didn't work for me in SQL Server.

Thanks, Sjoerd!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.