Re: Strange error in DELETE query

[Michael Stassen]

Jay Blanchard wrote:
[snip]
When I run the follow query:
DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)
only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
[/snip]
It is not a bug, just say it out loud....
"AND row_id is 2 OR 5 OR 7"
Once the OR condition is satisfied once, the query will halt. 
What are you talikng about?  Queries don't halt on the first row matched. 
For example:

  mysql> SELECT * FROM t;
  +-----------+--------+
  | client_id | row_id |
  +-----------+--------+
  |         1 |      1 |
  |         1 |      2 |
  |         1 |      3 |
  |         1 |      4 |
  |         1 |      5 |
  |         1 |      6 |
  |         1 |      7 |
  |         2 |      1 |
  |         2 |      2 |
  |         2 |      3 |
  |         2 |      4 |
  |         2 |      5 |
  |         2 |      6 |
  |         2 |      7 |
  +-----------+--------+
  14 rows in set (0.00 sec)
  mysql> SELECT * FROM t
      -> WHERE client_id = 1
      -> AND row_id IN (2,5,7);
  +-----------+--------+
  | client_id | row_id |
  +-----------+--------+
  |         1 |      2 |
  |         1 |      5 |
  |         1 |      7 |
  +-----------+--------+
  3 rows in set (0.01 sec)
  mysql> DELETE FROM t
      -> WHERE client_id = 1
      -> AND row_id IN (2,5,7);
  Query OK, 3 rows affected (0.00 sec)
  mysql> SELECT * FROM t
      -> WHERE client_id = 1
      -> AND row_id IN (2,5,7);
  Empty set (0.00 sec)
The question is: what do you (Ronan) mean by "only the first record is 
deleted"?  If you run

  SELECT * FROM table
  WHERE client_id = 1
  AND row_id IN (2,5,7)
how many rows do you get?
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]