DELETE Query?  
Author Message
Cleaning





PostPosted: Fri Aug 25 11:40:53 CDT 2006 Top

SQL Server Developer >> DELETE Query?

Assuming that a SQL Server 2005 DB table has a column named ID & one of
the ID records is 25, if the following DELETE query is executed, the
ID=25 record will get deleted from the DB table:

DELETE FROM Users WHERE ID=25

Now if the above DELETE query is executed again, then SQL Server just
generates the message "0 rows affected by last query". Why doesn't it
generate any error message since the record ID=25 no longer exists in
the DB table?

Thanks,

Arpan

SQL Server253  
 
 
Jim





PostPosted: Fri Aug 25 11:40:53 CDT 2006 Top

SQL Server Developer >> DELETE Query? Deletes and updates do not result in errors if no rows are affected. It is
simply the same the system works.

If you need to know if the record exists before trying to delete it, you
have to query the table first.



> Assuming that a SQL Server 2005 DB table has a column named ID & one of
> the ID records is 25, if the following DELETE query is executed, the
> ID=25 record will get deleted from the DB table:
>
> DELETE FROM Users WHERE ID=25
>
> Now if the above DELETE query is executed again, then SQL Server just
> generates the message "0 rows affected by last query". Why doesn't it
> generate any error message since the record ID=25 no longer exists in
> the DB table?
>
> Thanks,
>
> Arpan
>


 
 
Tom





PostPosted: Fri Aug 25 11:45:56 CDT 2006 Top

SQL Server Developer >> DELETE Query? Because it is not an error. You told it to delete all rows in Users Where
ID=25. If SQL Server can successfully do that, whether there are 0 rows, or
1 row, or 1,000,000 rows with ID=25, it will not return an error.

If you need to know in your T-SQL code whether or not a row was deleted,

error to your code when 0 rows were deleted, you can handle it there.

Tom



> Assuming that a SQL Server 2005 DB table has a column named ID & one of
> the ID records is 25, if the following DELETE query is executed, the
> ID=25 record will get deleted from the DB table:
>
> DELETE FROM Users WHERE ID=25
>
> Now if the above DELETE query is executed again, then SQL Server just
> generates the message "0 rows affected by last query". Why doesn't it
> generate any error message since the record ID=25 no longer exists in
> the DB table?
>
> Thanks,
>
> Arpan
>


 
 
--CELKO--





PostPosted: Fri Aug 25 17:26:50 CDT 2006 Top

SQL Server Developer >> DELETE Query? >> Now if the above DELETE query is executed again, then SQL Server just generates the message "0 rows affected by last query". <<

There is no error because rows are not records. SQL is a set-oriented
language; an empty sety is still a set. The statement did jsut what
you asked and then fired triggers, checked constraints, etc.

Now, if you did this in a file system and tried to read to the 25-th
record of an empty file, then you would get that kind of error. Look
at your older positngs; you are completely missing the foundations of
RM and trying to keep using file systems in your head. Get a good book
and take the time to learn the foundations.