Is this query safe in SQL Server?
- by xaw
I have this SQL update query:
UPDATE table1
SET table1.field1 = 1
WHERE table1.id NOT IN (SELECT table2.table1id FROM table2);
Other portions of the application can add records to table2 which use the field table1id to reference table1.
The goal here is to remove records from table1 which aren't referenced by table2.
Does SQL Server automatically lock table2 with this kind of query so that a new record can't be added to table2 while executing this query?
I've also considered:
UPDATE table1
SET field1 = 1
WHERE 0 = (SELECT COUNT(*) FROM table2 WHERE table1.id = table2.table1id);
Which seems possibly safer, but much slower (because a SELECT would be called on each row of table1 instead of just one select for the NOT IN)
