At 8:18 -0600 11/11/04, 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.
Why would it do that? DELETE doesn't execute just until it succeeds
in deleting one row. (Not without a LIMIT clause, at least.)
Anyway, here's my test:
DROP TABLE IF EXISTS t;
CREATE TABLE t (client_id INT, row_id INT);
INSERT INTO t VALUES(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
SELECT * FROM t;
DELETE
FROM t
WHERE client_id = 1
AND row_id IN (2,5,7);
SELECT * FROM t;
Result:
+-----------+--------+
| client_id | row_id |
+-----------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
+-----------+--------+
+-----------+--------+
| client_id | row_id |
+-----------+--------+
| 1 | 1 |
| 1 | 3 |
| 1 | 4 |
| 1 | 6 |
| 1 | 8 |
+-----------+--------+
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
