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...

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published.