SQL Server Performance

use update in conjunction with where in

Discussion in 'SQL Server 2005 General Developer Questions' started by i.netdeveloper, May 15, 2008.

  1. Hi,
    I want to update a table with dates for all users...
    so here is the script that i have written...Update
    employessSET
    DateOfTraining = trainingdatefrom
    temp_trainingwhere
    UserId in (Select U.UserId
    from temp_training tinner join Users U ON t.LastName = U.LastName and t.FirstName = U.FirstName)
    but it is updating all the users but with the very first date
    i want to update it on one to one basis
    userid trainingdate
    1 2/2/2008
    2 4/2/2008
    any help would be nice....thanks
  2. Adriaan New Member

    In an update query with a FROM clause with a join, you must include the "update table" in the FROM clause too, using (a) join(s).
    Your WHERE IN subquery only compares
    (1) the UserId values in Employees
    with
    (2) a UserId in temp_training whose FirstName and LastName also appear in the joined results of Users and temp_training.
    All rows in (1) are updated if they have a match in (2).
    For all rows in Employees, it takes the trainingdate value found on a random row in temp_training, and this just happens to be the first row.
    The subquery does not automagically join the Employees table to the temp_training table --- it only filters the rows in Employees.
    ***
    Always put the table name or the table alias before each and every column name - otherwise you'll never get these queries working.
    If you need to combine data from different tables in the results of your SELECT or UPDATE or INSERT query, you must include all these different tables in the FROM clause.
    ***
    The best trick is to write this out as a SELECT query that gives you the matching results, then you remove everything that is written before FROM, and you put the UPDATE clause there instead.
  3. Thank you for the answer..can u please update the my query...so that I can understand it better..
    thanks once again...
  4. Adriaan New Member

    If you don't understand the syntax, you should definitely not try and get quick answers.
    Please write a SELECT query that shows the current values from the Employees table, alongside the new values that you want to copy into those rows. Double-check on a couple of rows - not just the first row.
    Then remove the part before FROM, and write out the UPDATE statement before FROM.
    Make sure every single reference to a column includes the table name or alias. Do not assume that SQL Server knows which table you're suggesting, because the same column name will occur in more than one table.
  5. MichaelB Member

    In an effort to put it very simply, since he seems very inexperienced... when you update one table from another both tables need to be in the FROM statment and the table you want to update must be referenced after the UPDATE key word.
    Eg.

    update a
    set a.field1 = b.field2
    from table1 a
    inner join
    table2 b on
    a.id = b.id
  6. Hi,
    Thanks for the answer...it worked like charm....

Share This Page