update that involves 2 tables | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

update that involves 2 tables

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!
consider this: UPDATE maintable
SET legacyid=keytable.newid
FROM maintable INNER JOIN keytable
ON keytable.legacyid=maintable.legacyid
where keytable.legacyid is not null
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.
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=’:)‘ />
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.