Checking the Delete statement logic and the number of records deleted by it
Posted on 5/6/2007 6:49:14 AM
in #SQL SERVER
Hi,
A few days back a friend of mine asked me a simple but tricky question in SQL. The question was how would you check that the number of rows deleted through a delete query and the logic of the delete query is correct.
I know the first answer that comes to the mind the @@rowcount variable. But that variable is good enough for the row count only. How do you verify the login of the delete statement?
The answer is first we create a select query with the same clause that we want to delete the records. Now when we are satisfied with the login in the select statement, we use the delete statement with the same clauses. So here we have checked the logic of the delete with a select clause. After the delete we find the number of the rows deleted with the help of the @@rowcount variable. But this is not the end of the process. We should again use the Select statement with the same clause (as the delete statement). And this time the select statement will not return any records as all the records have been deleted with the previous delete statement. This way we can verify if the rows have been deleted with correct logic.
Thanks
Vikram
|
Posted on 5/17/2007 10:25:58 PM
|
Posted on 5/18/2007 5:47:20 AM
one fo the solution might be to count the number of rows that you may want to delete
ex:
SELECT count(*)
FROM suppliers
WHERE supplier_name = 'Microsoft';
this wil give you the count of the number of rows that you may want ot delete..and then perform your delete operation
ex:
DELETE FROM suppliers
WHERE supplier_name = 'Microsoft';
This would delete all records from the suppliers table where the supplier name is microsoft.
Again you want to count the number of rows with the supplier_name as Microsoft ,if you get the count as 0,then the logic of the delete query is correct........this operation is more of a kind of verifying the logic of delete query(getting the count of the rows before and after the delete operation)
Thanks
Aravind
|