June 20, 2008 at 8:26 am
SQL 2000 - I have two tables that I need to manipulate some data between. I need to develop an sql script that will do the following:
Update the ManagerID field in Table1 with the Supervisors ID from Table1 based on the persons defined Supervisor in Table2
All ManagerID values in Table1 start out at 0
Example for after script has been run:
John Doe's ManagerID in Table1 should be 50
Dave Smith's ManagerID in Table1 should be 51
Mary Jane's ManagerID in Table1 should be 52
Example Tables before script is run:
Table1
ID UserID Last_Name First_Name ManagerID
1 20 Doe John 0
2 21 Smith Dave 0
3 22 Jane Mary 0
50 Maint Sup Smith Bob 0
51 Prod Sup Wilson Kevin 0
52 Unit Oper Peters Gary 0
Table2
UserID Last_Name First_Name Supervisor
20 Doe John Maint Sup
21 Smith Dave Prod Sup
22 Jane Mary Unit Oper
June 20, 2008 at 11:55 am
Here is a select statement that will show how to get the information you are looking for. All you need to do is convert this into an UPDATE statement:
Select t1.Id
,t1.UserID
,t1.Last_Name
,t1.First_Name
,t2.Supervisor
,t3.Id As ManagerId
From #table1 t1
Inner Join #table2 t2 On t2.UserID = t1.UserID
Inner Join #table1 t3 On t3.UserID = t2.Supervisor
Where t1.ID < 50;
Jeff
Edit: Posted just a bit too soon...:)
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 1:50 pm
Hey Jeff,
I was able to resolve my issue with the following after some expert help of course. I learned a bit along the way which is great.
Hope this might help someone else with a similar request.
UPDATE t1
SET t1.ManagerID = t1_2.ID
FROM TLMUser t1
JOIN Supervisor_Tmp t2 ON t1.UserID = t2.UserID
JOIN TLMUser t1_2 ON t2.Supervisor = t1_2.UserID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply