Lance England

Safe UPDATE and DELETE

It seems like a familiar right of passage for all database people to execute an UPDATE or DELETE statement against an important table while accidentally forgetting the WHERE clause, thereby updating or deleting all rows in the table! What usually follows after the shock and sad realization is the Restore of Shame, where you get to restore the data.

I use a simple trick that helps guard against unfiltered updates/deletes. I certainly didn’t invent this technique; I simply thought it would be helpful to share. The more good practices we share with each other, the better for the community.

I use a common table expression (or CTE) to specify the records I want to update/delete. The steps are simple:

Simple, eh? For the extra cautious, since you can get the expected ROWCOUNT from your SELECT query, you can wrap the statement in a TRANSACTION. Here is an example for AdventureWorks:

First, let’s create a “copy” table, so as to not mess up AdventureWorks.

if OBJECT_ID('Production.Product_temp') is not null
    drop table Production.Product_temp
;
select *
into Production.Product_temp
from Production.Product;

Next, write your SELECT statement.

select *
from Production.Product_temp
where ProductModelID = 23;

Then wrap it in a CTE and write your UPDATE statement against it (and not the base table!)

with cte_safeUpdate as
(
     select *
     from  Production.Product_temp
     where ProductModelID = 23
) update cte_safeUpdate
SET ProductModelID = NULL;

As I mentioned, you can also wrap a transaction around it

begin tran;
with cte_safeUpdate as
(
    select *
    from  Production.Product_temp
    where ProductModelID = 23
)
update cte_safeUpdate
SET ProductModelID = NULL;
if @@ROWCOUNT = 10
    commit;
else
    rollback;

The concept works the same for DELETE statements.

with cte_safeDelete as
(
    select *
    from  Production.Product_temp
    where ProductModelID = 23
)
delete from cte_safeDelete;

Feel free to share your own tips or suggestions. Hope this helps someone avoid the ‘restore of shame’!

25 Jul 2014 Permalink data