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.
It should came back with 1 row.
select First, Last, Middle from dbo.tblEmployees where Last = 'McDonald'
Now when you run this, it had better come back with only 1 row affected!
update dbo.tblEmployees set First = 'Ronald', Modify_Date = GETDATE() where Last = 'McDonald'
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.
Now when you run this the first time you'll see 1 row affected.
update dbo.tblEmployees set First = 'Ronald' Modify_Date = GETDATE() where Last = 'McDonald' and First = 'Ron'
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.