Best Practices for Confirming Success of Update Queries

update

If I am doing a SQL Update query that affects, for instance, 1000 rows, how do I check that the query worked as intended and didn't hit any strange edge cases, (short of examining each row visually myself)?
What is best practice for this ?

Additional info requested in comments
I was writing a SQL query to update any fields in a column that were blank with a standard value. That by itself is pretty easy and the number of fields were small enough that I could check them visually. But I figured there could be much more complex queries covering larger numbers of fields where examining wasn't feasible.

Best Answer

You could query before and after looking for the particular conditions you are wanting to update.

For adhoc queries it's a good idea to run the query as a select statement using the same where clause before running as an update statement.

The update should update exactly the same rows select returned unless the data has changed. 

create table #test ([id] int identity (1,1), [status] varchar(50) )

insert into #test VALUES ('COMPLETED')
insert into #test VALUES ('FAULTED')
insert into #test VALUES ('COMPLETED')
insert into #test VALUES ('FAULTED')
insert into #test VALUES ('FAULTED')    

select status
from #test 
where status = 'FAULTED'

select count(*) 
from #test 
where status = 'FAULTED'

update #test 
set status = 'FAILED'
where status = 'FAULTED'

select count(*) 
from #test 
where status = 'FAULTED'

select status, count(*) 
from #test 
group by status
Related Question