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 (
from UserAccount with (nolock )
inner join employee with (nolock) on employee.personId = UserAccount.PersonId
where EmployerId =66 )
>> 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
DELETE FROM UserAccounts
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.