0

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.

  • 1
    Can you give us some examples? Are you concerned about more rows or less being updated? or the update syntax being messed up? – Sir Swears-a-lot Dec 5 '17 at 3:37
  • 1
    Which DBMS product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. – a_horse_with_no_name Dec 5 '17 at 6:52
  • For logical validation (optimistic locking) you would use the update count, for physical validation it is enough to not get an error on (auto)commit. – eckes Dec 6 '17 at 5:19
2

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
|improve this answer|||||
1

Script belong to Sql Server. but my above idea is for any DBMS.

Main goal should be fullfilling requirement, error free Script and best performance.

Why would I want to know everytime that whether those 1000 rows were updated sucessfully or not ? why would i want to know about each rows ?

when my script is properly tested and it has pass through QA phase.

Why i will write unnecessary code to verify when it is not actual biz. requirement ?

If it is not actually biz. requirement then I write update Sql within Try-Catch and return simple output int para,indicating pass/fail.

Also log the exception in catch.review your log table.

in case you really need to know the updated output then OUTPUT Clause is very handy in sql server.

create table #t (id int identity(1,1),col int)
insert into #t values(1),(2)
declare @Outputtbl table (col int);

update #t
set col=3
output inserted.col into @Outputtbl

select * from @Outputtbl

--select * from #t

drop table #t
|improve this answer|||||
  • Thank you for your advice. At my current employer, there is not a formal testing or QA phase, (and there would be no one to do it), The suggestion of the OUTPUT clause could be useful though. – chrisfs Dec 6 '17 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.