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 )

remove the with (nolock), as it can return incorrect, missing, or duplicate
data if the table gets updated while the query is running.  There was a link
to an article on the topic posted last week, unfortunately I cannot find the
message now, but a quick search will turn up lots of info on the pitfalls of
NOLOCK.

Show quoteHide quote

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

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.

Best way to insert data into tables without primary keys
What is new in Sql Server 2005 cursors?
error converting datatype varchar to numeric
Update Query in SQL 2005 with inner join
nesting select statements