Test your UPDATEs and DELETEs before you run them

February 18, 2015 by Kenneth Fisher

This isn’t so much of a best practice, it comes more under the heading of being careful. Let’s say you have a DELETE statement (or UPDATE) that you need to run. You can certainly put it in a transaction. Run it, test it, then if you’ve made a mistake roll it back. Of course lets say the DELETE takes half an hour, and then another hour to roll back. You have now just lost an hour and a half. Instead why don’t you convert your query to a SELECT, maybe start with a COUNT(1) to check that the number of rows is at least in the right ballpark, then a TOP 100 or so to make sure you are hitting the right data, etc.

Now, you may be thinking “Won’t that be a lot of work to re-write my query each time?” No, not really. It’s actually pretty simple. Watch:

-- DELETE version
DELETE 
FROM [Sales].[Store]
WHERE [Store].[BusinessEntityID] NOT IN 
		(SELECT BusinessEntityId
		 FROM [Person].[BusinessEntityContact])
-- UPDATE version
UPDATE [Sales].[Store] SET BusinessEntityID = NULL
WHERE [Store].[BusinessEntityID] NOT IN 
		(SELECT BusinessEntityId
		 FROM [Person].[BusinessEntityContact])
-- SELECT version
SELECT * 
FROM [Sales].[Store]
WHERE [Store].[BusinessEntityID] NOT IN 
		(SELECT BusinessEntityId
		 FROM [Person].[BusinessEntityContact])

They aren’t really all that different are they? To be fair this isn’t going to catch all possible mistakes but it’s certainly going to catch some. More than enough to be worth the extra effort.

Now you can also go the other direction. Let’s say you need complex logic for a DELETE or UPDATE. Build a SELECT first. Then once your query is perfect just modify it into an UPDATE or DELETE.

When I suggest this I usually get similar concerns about the difficulty (it’s easy). I also get questions about dealing with alias’ which are somewhat common in more complex queries. Still not a problem as I discussed here and here. Believe it or not I’ve never run across a query I couldn’t easily convert between a SELECT, DELETE or UPDATE. In fact in some ways going from a SELECT to a DELETE or UPDATE is even easier than going the other way.

Make sure that the table you want to UPDATE or DELETE from is the table right after the FROM. You can reference it in one or more JOINs as well but it has to at least but the FROM table.
```
-- Step 1
SELECT Field1, Field2
FROM DeleteOrUpdateTable
```

Write your SELECT to pull exactly the rows you want to DELETE or UPDATE.

-- Step 2
WITH MyCTE AS (SELECT Field1 FROM AnotherTable 
		WHERE Conditions = 'True')
SELECT Field1, Field2
FROM DeleteOrUpdateTable DoUT
JOIN MyCTE
	ON DoUT.Field1 = MyCTE.Field1
WHERE MoreConditions = 'True'

Delete the SELECT FieldList part of the SELECT. Note: If you have CTE(s) they will stay in the same place (at the beginning of the query). Only delete SELECT and the fieldlist.

-- Step 3
WITH MyCTE AS (SELECT Field1 FROM AnotherTable 
		WHERE Conditions = 'True')

FROM DeleteOrUpdateTable DoUT
JOIN MyCTE
	ON DoUT.Field1 = MyCTE.Field1
WHERE MoreConditions = 'True'

Put DELETE/UPDATE in place of the SELECT and then the table you want to affect right after that.
Note: If you aliased the table in the FROM clause use the alias here.
Note 2: You can reference a CTE, to DELETE or UPDATE, under the right circumstances. I’m not sure of all the conditions but at the very least it can only reference a single table (no JOINs).

-- Step 4
WITH MyCTE AS (SELECT Field1 FROM AnotherTable 
		WHERE Conditions = 'True')
DELETE DoUT
FROM DeleteOrUpdateTable DoUT
JOIN MyCTE
	ON DoUT.Field1 = MyCTE.Field1
WHERE MoreConditions = 'True'
-- or 
WITH MyCTE AS (SELECT Field1 FROM AnotherTable 
		WHERE Conditions = 'True')
UPDATE DoUT
FROM DeleteOrUpdateTable DoUT
JOIN MyCTE
	ON DoUT.Field1 = MyCTE.Field1
WHERE MoreConditions = 'True'

If you are doing an UPDATE then you want to put the SET statement between the table name/alias and the FROM clause.

-- Step 5 (Only if it's an UPDATE)
WITH MyCTE AS (SELECT Field1 FROM AnotherTable 
		WHERE Conditions = 'True')
UPDATE DoUT
	SET Field2 = 'SomeString'
FROM DeleteOrUpdateTable DoUT
JOIN MyCTE
	ON DoUT.Field1 = MyCTE.Field1
WHERE MoreConditions = 'True'

See, not hard at all. Give it a shot yourself. Do it yourself once or twice and you will realize just how easy it really is.

Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, language sql, microsoft sql server, sql statements, T-SQL

15 thoughts on “Test your UPDATEs and DELETEs before you run them”

johndeardurff says:

February 18, 2015 at 7:10 AM

This is one of my Golden Rules when I teach DML. 1. Always write an UPDATE/DELETE as a SELECT statement first. 2. Always use a WHERE statement. The job you save may be your own.

Kenneth Fisher says:

February 18, 2015 at 9:19 AM

I always love it when you tell me stuff like that. I’ve never taken a formal class on databases so it’s nice to get validation that my best practices match up with someone who teaches the stuff 🙂

Reply

davidbainbridge83 says:

February 18, 2015 at 7:18 AM

Awesome suggestion. My strategy is to wrap deletes and updates in a transaction with a rollback so I can see what changes before a firm commit:

BEGIN TRANSACTION

— Delete
DELETE FROM …

— Check what has changed
SELECT * FROM …

ROLLBACK TRANSACTION

As an aside I went to a SQL server user group the other day and your blog came up (this is in the UK).

Kenneth Fisher says:

February 18, 2015 at 9:25 AM

Transactions are a great method for this kind of test, however they do cause you to create and hold stronger locks than you would by using the SELECT. For example with a decent sized delete you might have just locked up the table while you are doing your checkout.

I wouldn’t discourage using transactions (far from it) but if you do the SELECT statement first you can spend a longer time checking the output, then run your delete with a transaction and do a quick confirmation that you got what you expected and then commit.

I’m hoping that anything said about my posts was at least polite? I’ve heard British people curse before (my parents are from London) and it can be a bad thing 🙂

Reply

Anders Pedersen says:

February 18, 2015 at 10:04 AM

I always write deletes as selects first. And even then when I test the deletes I always put transaction around it. Off course I also a long time ago, due to school of hard knocks education, started putting a begin transaction at the top of my query window if I am writing deletes or updates. Just in case my fingers slipped and ran the entire query before I was ready with the complete where clause…. That was NOT a happy day.

Kenneth Fisher says:

February 18, 2015 at 12:11 PM

I started with Foxpro (may years ago) where you periodically had to send a special command to clean up deleted material. The DELETE command only set an “IsDeleted” flag. It took many years before I got over the “I know I deleted it but now I want it back so where is it” mentality.

Reply

Gary Harding says:

February 19, 2015 at 4:24 AM

Good and worthwhile post. Of course, formulating and testing a complicated DELETE or UPDATE query is ideally done on a Test or Dev server, well away from the operational system. But if you are doing it on live data, wrapping the query in a transaction with ROLLBACK while you’re working on it is a good safety net. After all, that F5 key is only an inch away.

Kenneth Fisher says:

February 19, 2015 at 9:38 AM

I 100% agree that any statement you are going to run on production should have been tried on dev & test first. However when you are talking about deletes (and updates, and inserts for that matter) there is a good chance that the data is going to be different, causing potentially different results. That means that while you may have tested out your query and are certain it will work structurally you can’t be sure it will work data wise and it’s a good idea to test (yes again) with a SELECT before running in Prod.

Now you do see environments where the dev/test environments are identical to production and refreshed on a regular basis but those have issues of their own (particularly with security) and so you don’t see them too often.

Thanks for the comment! And yes, that F5 key is way to easy to hit accidentally 🙂

Reply

twoknightsthenight says:

February 21, 2015 at 8:53 AM

Another way to do this is to set it up so that ROLLBACK is a default and perform extra default logic that leaves an audit trail to look at your work. I have loads of test programs, procedures and the like for DB access and always set a “test flag” and check it. Once ready, simply comment it out and leave a note explaining how you could test it. I never remove the rollback code, I simply comment it out and leave it ready in case someone else has to work on it.

I work as a DBA so I don’t write a LOT of code but a lot of what I do write is dangerous and used for diagnostics and troubleshooting and cannot always be tested anywhere but a production system. .

Kenneth Fisher says:

February 21, 2015 at 7:07 PM

You probably aleady know this but the only thing I would warn you about is that as a DBA at least some of your commands are not going to be affected by transactions and at least in some cases you won’t even get an error.

https://sqlstudies.com/2014/02/05/transactions-what-commands-arent-allowed/

Reply

Ryk McDorman says:

February 21, 2015 at 4:32 PM

I learned this the hard way years ago when I deleted millions of rows in a table because my WHERE clause wasn’t correct. I had to do a point-in-time restore of the database and luckily didn’t lose anything except an hour and a half of time. Since then, it’s SELECTs and BEGIN TRANSACTIONs for me!

Kenneth Fisher says:

February 21, 2015 at 7:07 PM

Learning that way is one of the most painful ways to learn, but boy you don’t frequently forget.

Reply

Ron Van Aken says:

February 23, 2015 at 1:41 PM

Thank you for the posting. Your first code section reminded me of something I started doing a while back. It’s not going to work in your CTE permutations, but… I commonly add a safety to my delete scripts like this:

SELECT *
— DELETE
FROM …

You have to deliberately highlight the DELETE and the rest of the query without the comment. Of course it’s not completely fool-proof, but it protects against the stray F5 and it also guarantees that your SELECT and DELETE commands are identical. Failing to highlight your WHERE clause would be painful, but that is always a theoretical risk. I am not claiming credit for it for this idea… just passing it on.

Kenneth Fisher says:

February 23, 2015 at 1:44 PM

Good suggestion. Actually it will work with CTEs if you do it just slightly different. Instead of highlighting the bit you want uncomment the DELETE and comment the SELECT then run. Then as soon as you run undo 2ce to “fix” the comments back.

Reply