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:
Selectt1.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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy