DELETE Query?

25 Aug 2006 4:30 PM

Arpan

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

25 Aug 2006 4:40 PM

Jim Underwood

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.

Show quote

"Arpan" <arpan***@hotmail.com> wrote in message
news:1156523403.320456.220740@m73g2000cwd.googlegroups.com...
> 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
>

25 Aug 2006 4:46 PM

Tom Cooper

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,
just check @@RowCount immediately after the delete statement. If it is an
error to your code when 0 rows were deleted, you can handle it there.

Tom

Show quote

"Arpan" <arpan***@hotmail.com> wrote in message
news:1156523403.320456.220740@m73g2000cwd.googlegroups.com...
> 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
>

25 Aug 2006 10:26 PM

--CELKO--

>> 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.