SQL Server Performance

Advanced (for me) Query help

Discussion in 'General Developer Questions' started by tubbscc, Jun 20, 2008.

  1. tubbscc New Member

    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
  2. Adriaan New Member

    Looks like a classroom excercise - in which case you shoudn't be cheating.[:#]
  3. tubbscc New Member

    lol..I promise I'm not. This is for work and I've had no formal training.[:D]
    My days in school are long gone, now it's all ojt.
  4. Adriaan New Member

    Fair enough. But this is pretty basic query stuff - execute an UPDATE query that includes a FROM clause with a JOIN.
    See if you can write a SELECT query that returns the results for the key columns next to eachother. Now you already have the FROM and JOIN parts, and all you have to do is rewrite the SELECT part into UPDATE and SET clauses.
  5. Luis Martin Moderator

    "Fair enough. But this is pretty basic query stuff - execute an UPDATE query that includes a FROM clause with a JOIN.

    See if you can write a SELECT query that returns the results for thekey columns next to eachother. Now you already have the FROM and JOINparts, and all you have to do is rewrite the SELECT part into UPDATEand SET clauses."
    [:)]
  6. tubbscc New Member

    I was able to come up with this with some excellent help from some more experienced users.
    The real tables are:
    Table1 = TLMUser
    Table2 = Supervisor_Tmp
    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
    The only problem I have now is that there are 47 rows that should get updated but only 33 get updated. The 33 are perfect by the way, I'm trying to figure out why the others are being ignored.
    It was suggested that the UserID from TLMUser may not match the UserID from Supervisor_Tmp. I've looked and compared line by line and they do appear to match. Trying to track down what could be different between the two.
    Any ideas would be greatly appreciated.
  7. tubbscc New Member

    The problem was in fact that the UserID from TLMUser may not match the UserID from Supervisor_Tmp I had to clean up the data before importing it again. After everything matched, the script above did exactly what it was supposed to do.
    Thanks to everyone that reponded, hopefully this may help someone else with a similar issue.

Share This Page