SQL Server Performance

update that involves 2 tables

Discussion in 'SQL Server 2005 General Developer Questions' started by Phthisis, Dec 18, 2006.

  1. Phthisis New Member

    Hello all, I have a question about doing an update statment that involves 2 tables; a Key table and the table that is to recieve the key update.

    Basically I have the duty to update an old key to a new key. All the keys are unique and mapped (eg: oldkey and newkey are the fields) and all i have to do is update the main table.

    currently i am having issues coming up with the working syntax so i am here looking for your help.

    here is what i have so far:




    update maintable set maintable.legacyid = (select newID from keytable where maintable.legacyid = keytable.legacyid)
    where keytable.legacyid is not null



    this is the last incantation of my attempt.

    thank you in advance!
  2. ranjitjain New Member

    consider this:

    UPDATE maintable
    SET legacyid=keytable.newid
    FROM maintable INNER JOIN keytable
    ON keytable.legacyid=maintable.legacyid
    where keytable.legacyid is not null
  3. Adriaan New Member

    Read the error message carefully. It will say something along the lines that it cannot recorgnize the legacyid column, referring to the third line of your query.

    The problem is that you have a main query which is an update on maintable, and a subquery which is a lookup from keytable. The WHERE clause of your main query refers to a column from keytable, but this table is only mentioned in the FROM clause of the subquery, so it is 'unknown' in the main query.

    When using the UPDATE syntax, make a habit of adding a FROM clause:

    UPDATE M
    SET M.LegacyId = K.NewId
    FROM dbo.MainTable M
    INNER JOIN dbo.KeyTable K ON M.LegacyId = K.LegacyId

    Most examples for the UPDATE syntax are for single-table statements, and you may think you need a subquery. But in fact you can use a FROM clause exactly like in SELECT query (still with the restriction that you can update columns in only one of the tables).

    Note that the table aliases from the FROM clause must be used throughout the statement.

    The INNER JOIN also makes sure that only matching LegacyId values get updated, so no need to check for NULL.
  4. Phthisis New Member

    would a left outer join be more appropriate than an inner join? If there are extra keys on the key table will that create issues with update using an inner join?<br /><br />Thanks for all your help guys <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  5. ranjitjain New Member

    Hi,
    The task here is to update all the legacyid of maintable to newid matching with keytable
    So you dont need left join here anyway.
    If you use left join, then lagacyid from maintable who does not have matching entry in keytable will have NULL against them, if is this what you need then use left join else you should go with inner join
    and as adriaan said, there is no need for where clause to filter null rows of keytable.

Share This Page