Delete with inner join

SQL Apprentice Question
Is this the proper way of doing a join in a delete or update?
Is the a better way?
delete useraccount where useraccountid in (
select useraccountid
from UserAccount with (nolock )
inner join employee with (nolock) on employee.personId = UserAccount.PersonId
where EmployerId =66 )

Celko Answers

>> Is this the proper way of doing a join in a delete or update? <<


There is no proper way of oing a join in a delete or update; that
syntax is proprietary and subject to errors.


>> Is the a better way? <<


Assuming that you actually have more than one user account and that you
are concerned with the set of Personnel and not a single employee,
let's use names that follow ISO-11179 rules. You might be able to do
this:

DELETE FROM UserAccounts
WHERE EXISTS
(SELECT *
FROM Personnel AS P
WHERE UserAccounts.person_id = P.person_id
AND P.employer_id = 66);


This can use indexes instead of being forced to materialize a JOIN
result. You can drop the WITH (NOLOCK) options since a statement is
treated as a transaction. Think about predicates and not about
creating table. That is how we did data processing with scratch tapes
in the old days.