List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 27 2001 6:35pm
Subject:Re: Bugreport: Incorrect rows affected number!
View as plain text  
In the last episode (Oct 27), Gunter Leeb said:
> Below sequence shows that MySQL returned 0 rows affected after it
> deleted 2 records.
> mysql> delete from mmecounter;
> Query OK, 0 rows affected (0.00 sec)

From the manual:

    If you issue a `DELETE' with no `WHERE' clause, all rows are
    deleted.  If you do this in `AUTOCOMMIT' mode, this works as
    `TRUNCATE'. *Note TRUNCATE::.  In MySQL 3.23 `DELETE' without a
    `WHERE' clause will return zero as the number of affected records.

    If you really want to know how many records are deleted when you
    are deleting all rows, and are willing to suffer a speed penalty,
    you can use a `DELETE' statement of this form:

         mysql> DELETE FROM table_name WHERE 1>0;

Mysql 4 does an internal "SELECT COUNT(*)" before deleting all the
rows, so it can return a delete count in this case.

	Dan Nelson
Bugreport: Incorrect rows affected number!Gunter Leeb27 Oct
  • Re: Bugreport: Incorrect rows affected number!Dan Nelson27 Oct