Don’t let this little SQL slip delete your rows!

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