Populate ReferenceID in table - SQL Server Forums

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)

Populate ReferenceID in table

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-21 : 09:18:24

I want a query that will populate the ReferralUserID in the table below. ReferralUserID can be populated by referencing the ReferralUserName and doing a lookup for the UserID.

So ReferralUserID is the UserID of the username under ReferralUserName.

TableUsers

UserID UserName ReferralUserName ReferralUserID
1 John Jerry NULL
2 Mark Mommy NULL
3 Jerry John NULL
4 Donny Mark NULL
5 Mommy John NULL

Expected Output for ReferralUserID:

3 (Jerry)
5 (Mommy)
1 (John)
2 (Mark)
1 (John)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-21 : 09:57:55

[code]
declare @TableUsers table (UserID int, UserName varchar(50), ReferralUserName varchar(50), ReferralUserID int)
insert into @TableUsers
select 1, 'John', 'Jerry', NULL union all
select 2, 'Mark', 'Mommy', NULL union all
select 3, 'Jerry', 'John', NULL union all
select 4, 'Donny', 'Mark', NULL union all
select 5, 'Mommy', 'John', NULL

update t1
set ReferralUserID = t2.userid
from @TableUsers t1
left join @TableUsers t2 on t2.UserName = t1.ReferralUserName

select * from @TableUsers
[/code]

Go with the flow & have fun! Else fight the flow

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-21 : 12:22:47

thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources