SQL Server DELETE FROM: The World’s Scariest DELETE statement


It’s out there. Waiting for you. While you sleep, it’s awake. It’s coming for you. It’s the Jason Voorhees of SQL Server and you’re the teenager headed into the woods on a dark night…

How can you stay safe?  Well my friend, knowledge is power.  (For example, if you’re a teenager don’t go to Crystal Lake. It will not end well.)

Okay, check it out.  We’ll create a little table for an example and then show how you’re friendly neighborhood developer might put together a delete statement to fix a little production support problem.  Luckily, because knowledge is power, you’ve already revoked this developers perms in production, so he will be sending the little bit of code your way, disguised as legitimate looking DELETE.

Create a table and throw a few rows in there:

IF OBJECT_ID('DangerousDelete') IS NOT NULL 
    BEGIN
        DROP TABLE DangerousDelete
    END

CREATE TABLE DangerousDelete ( col1 CHAR(1) )

INSERT  DangerousDelete
        ( col1 )
VALUES  ( 'A' )
        ,( 'B' )
        ,( 'C' )
        ,( 'D' )

Now here’s the scenario: your friendly developer realizes there is a problem in production and by deleting row B from the table it can be resolved.  First, the developer writes a quick select to make sure they grab the right row:

SELECT  *
FROM    DangerousDelete
WHERE   col1 = 'B'

Now that our developer has verified everything is working as expected he adds the delete syntax, wisely building it on the SELECT which has been verified as correct.  <cue scary music now…>

DELETE FROM DangerousDelete
SELECT  *
FROM    DangerousDelete
WHERE   col1 = 'B'

If the happy developer remembers to comment out their select there’s no problem. The following statement will work exactly as expected and delete one row:

DELETE FROM DangerousDelete
--SELECT  *
FROM    DangerousDelete
WHERE   col1 = 'B'

But, if in a moment of distraction your developer forgets to comment out the select and sends you the following:

DELETE FROM DangerousDelete
SELECT  *
FROM    DangerousDelete
WHERE   col1 = 'B'

Beware! You are being led deep into the woods… The statement above will delete EVERY row in the table!  Noooooooooo!

(Just a reminder it’s always a good practice to BEGIN TRAN before running an ad hoc statement. This will allow you you to rollback when the ROWS AFFECTED comes back with more than you anticipated!)

 

Beware of Jason and his DELETE FROM
Sure Jason! I’ll run your DELETE as soon as I finish my swim…

Leave a Reply

Your email address will not be published.