Wednesday, August 14, 2013

SQL Updates - One shot

Let's be honest, we've all made that mistake in SQL where we accidentally
forgot to put the right parameters into a where clause, and had to
run down to the dba and beg for a recent backup!

Writing SQL update statements really isn't that difficult,
but sometimes it can be daunting executing them,
depending on how many records you're looking at.

There are a few ways you can make things a little less intimidating.

1 - Work up your where clause in a select statement first, to see how many records would be affected.

For a simple example, let's say Ronald McDonald is in our database as "Ron",
and we want to update to use his full name.

select First, Last, Middle
from dbo.tblEmployees
where Last = 'McDonald'
It should came back with 1 row.
update dbo.tblEmployees
set First = 'Ronald',
    Modify_Date = GETDATE()
where Last = 'McDonald'
Now when you run this, it had better come back with only 1 row affected!

2 - Write your where clause so that if you ran it a second time it would affect 0 rows.

Taking the same example, it's good practice to make sure that
if you run your stored procedure twice, it will not do any updates the second time.

update dbo.tblEmployees
set First = 'Ronald'
    Modify_Date = GETDATE()
where Last = 'McDonald'
    and First = 'Ron'
Now when you run this the first time you'll see 1 row affected.
If you run it a second time, it will show 0 rows affected.
Note that it won't overwrite the Modify date, so you'll preserve the date of the original update.

I call this method a one-shot. It can be very useful if you need to track the history of updates,
and especially useful if you have things like update triggers on your table.

No comments:

Post a Comment