Help with looping through records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with looping through records

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2008-04-17 : 11:15:13

My problem, I'm pretty sure, is the fact that I'm not looping through my records, and not sure how to go about doing this. I am trying to update 1 table based on the values of another table.

Table 1:
EmpName userNumber
John Doe Null
Jane Smith Null
Bob Green Null

Table 2:
userName UserNumber
John Doe 20
Jane Smith 30
Bob Green 40

Desired Updated Table 1:
EmpName userNumber
John Doe 20
Jane Smith 30
Bob Green 40

Code so far - updates all usernumber with 40:
Declare @userID int

Select @userID = userNumber from users
where username in
(Select RefEmp from Referral)

So my code actually returns:
Table 1:
EmpName userNumber
John Doe 40
Jane Smith 40
Bob Green 40

Can anyone help?
Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 11:20:14

why loop
update Table1
set usernumber = t2.usernumber
from Table1 t1
join Table2 t2
on t2.username = t1.empname

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ann
Posting Yak Master

220 Posts

Posted - 2008-04-17 : 11:26:05

Thanks - that worked.

As to why I didn't do it your way.. just didn't think to do it that way is all :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources