I came across this quick example that shows how easily the tiniest little mistake could end up deleting rows from your table.
The solution to this bug? Always name & reference your tables, CTE, and subqueries!
-- Create and populate test tables. drop table if exists dbo.purge_test; create table dbo.purge_test( id int primary key, purge_type int not null default 1, purge_date datetime not null default current_timestamp ); insert into dbo.purge_test(id) values(99); drop table if exists dbo.star_wars_film; create table dbo.star_wars_film ( film_id int primary key, film_description varchar(50) not null ); insert into dbo.star_wars_film(film_id, film_description) values (1,'Star Wars - The phantom menace'), (2,'Revenge of the Sith'), (3,'Attack of the clones'), (4,'A new hope'), (5,'The empire strikes back'), (6,'Return of the Jedi'), (7,'The force awakens'), (8,'The last Jedi'), (-1,'Rogue One');
And now you can play: Spot the Bug™!
-- Check the data before the unspotted bug. select film_id, film_description from dbo.star_wars_film order by film_id; -- Bug! Do you see it? The query runs just fine.. right? delete from dbo.star_wars_film where film_id in ( select film_id from dbo.purge_test where purge_type = 1 ); -- After the delete. -- Expected the same rows as before because we *know* the purge id of 99 wasn't one of the file_id. -- Right? select film_id, film_description from dbo.star_wars_film order by film_id; -- Corrected query. Won't execute now because specified column doesn't exist. delete swf from dbo.star_wars_film swf where swf.film_id in ( select pt.film_id from dbo.purge_test pt where pt.purge_type = 1 );