Rajan John (8/27/2008)
For such scenarios, I also do the updates with a join only. I would be interested to see whether this approach has any performance implications, and what are other recommended ways?
While the TSQL update statement can be useful it has the potential problem of not thowing an error if there are multiple results when you were expecting a single result. In this case an employee could have multiple passports so, as there is no guarantee that the last passport will be returned last, the employee row could have the passportcode of an expired passport. Someone should at least ask the question of whether this matters before the TSQL JOIN sysntax is used. If you are expecting Passport.EmployeeId to be unique it could be argued that an UNIQUE constraint on Passport.EmployeeId would solve the problem but can you really rely on that constraint not being removed sometime? In this case I would be inclined to use the ANSI update syntax so that an error would be thrown if the data was not as expected. The performance may be worse than the TSQL JOIN syntax. eg:
UPDATE Employee
SET passportcocde =
(
    SELECT P.passportcode
    FROM Passport P
    WHERE P.EmployeeId = Employee.EmployeeId
)
WHERE EXISTS
(
    SELECT *
    FROM Passport P1
    WHERE P1.EmployeeId = Employee.EmployeeId
)