{"id":387,"date":"2012-11-30T12:55:56","date_gmt":"2012-11-30T19:55:56","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=387"},"modified":"2012-11-14T12:44:37","modified_gmt":"2012-11-14T19:44:37","slug":"sql-server-delete-from-the-worlds-scariest-delete-statement","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/11\/sql-server-delete-from-the-worlds-scariest-delete-statement\/","title":{"rendered":"SQL Server DELETE FROM: The World’s Scariest DELETE statement"},"content":{"rendered":"

It's out there. Waiting for you. While you sleep, it's awake. It's coming for you. It's the Jason Voorhees<\/a> of SQL Server and you're the teenager headed into the woods on a dark night...<\/p>\n

How can you stay safe?\u00a0 Well my friend, knowledge is power.\u00a0 (For example, if you're a teenager don't go to Crystal Lake<\/a>. It will not end well.)<\/p>\n

Okay, check it out.\u00a0 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.\u00a0 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.<\/p>\n

Create a table and throw a few rows in there:<\/p>\n

IF OBJECT_ID('DangerousDelete') IS NOT NULL \r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DROP TABLE DangerousDelete\r\n\u00a0\u00a0\u00a0 END\r\n\r\nCREATE TABLE DangerousDelete ( col1 CHAR(1) )\r\n\r\nINSERT\u00a0 DangerousDelete\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( col1 )\r\nVALUES\u00a0 ( 'A' )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,( 'B' )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,( 'C' )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,( 'D' )<\/pre>\n
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.\u00a0 First, the developer writes a quick select to make sure they grab the right row:<\/p>\n
SELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 DangerousDelete\r\nWHERE\u00a0\u00a0 col1 = 'B'<\/pre>\n
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.\u00a0 <cue scary music now...><\/p>\n
DELETE FROM DangerousDelete\r\nSELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 DangerousDelete\r\nWHERE\u00a0\u00a0 col1 = 'B'<\/pre>\n
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:<\/p>\n
DELETE FROM DangerousDelete\r\n--SELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 DangerousDelete\r\nWHERE\u00a0\u00a0 col1 = 'B'<\/pre>\n
But, if in a moment of distraction your developer forgets to comment out the select and sends you the following:<\/p>\n
DELETE FROM DangerousDelete\r\nSELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 DangerousDelete\r\nWHERE\u00a0\u00a0 col1 = 'B'<\/pre>\n
Beware! You are being led deep into the woods... The statement above will delete EVERY row<\/strong> in the table<\/strong>!\u00a0 Noooooooooo!<\/p>\n
(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!)<\/p>\n
 <\/p>\n
\"Beware<\/a>
Sure Jason! I'll run your DELETE as soon as I finish my swim...<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
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?\u00a0 Well my friend, knowledge is power.\u00a0 (For example, if you're a teenager don't go to Crystal […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,57],"tags":[175,97,96,95,10,6,9,12,11,173,5],"class_list":["post-387","post","type-post","status-publish","format-standard","hentry","category-microsoft-sql-server","category-t-sql","tag-dba","tag-delete","tag-friday-the-13th","tag-jason","tag-ms-sql","tag-script","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/387","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=387"}],"version-history":[{"count":7,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/387\/revisions"}],"predecessor-version":[{"id":404,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/387\/revisions\/404"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=387"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=387"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=387"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}