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
);