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.

Ex:

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.

Ex:

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.