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.

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

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

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

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

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

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL