At 9:45 -0600 3/8/03, Mark Matthews wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[EMAIL PROTECTED] wrote:
Description:
It is unbelievable that the MySQL ver 4.0 have so many bug, I
have been reported 2 bugs just a few days ago.
Now, I have found a bug again.
The bug is :
When I execute "select * from old_topic where FID=4 and (page=0 or
page=167) order by replytime asc limit 40;" returns 40 rows[40 rows
in set (0.01 sec)]
23 rows value of the column "page=167", and and 17 are "page=0".
Then I execute "update old_topic set page=167 where FID=4 and
(page=0 or page=167) order by replytime asc limit 40;"
##########
mysql> update old_topic set page=167 where FID=4 and (page=0 or
page=167) order by replytime asc limit 40;
Query OK, 40 rows affected (7.75 sec)
Rows matched: 61 Changed: 40 Warnings: 0
#########
It tells that 40 rows are updated, but it is incorrect, as there
should only 17 rows are affected because the "limit 40"
############
mysql> select count(*) from old_topic where FID=4 and page=167;
+----------+
| count(*) |
+----------+
| 61 |
+----------+
1 row in set (0.01 sec)
############
[snip]
Hi!
I think this is a misunderstanding of how/why LIMIT applies to
updates. It's not exactly the same as when used for SELECTs. The
limit applies to the number of rows _changed_, not examined. Here's
the relevant section from the manual:
http://www.mysql.com/doc/en/UPDATE.html
Paul DuBois will probably correct me if I'm wrong, but if you want
to have the behavior you expect, you will either have to do it from
your program based on the SELECT you have issued, or use MySQL-4.1
which has subqueries.
I'm with Mark. A LIMIT clause in an UPDATE statement limits the number
of records updated, just as a LIMIT clause in a DELETE statement limits
the number of records deleted. This is consistent with a LIMIT in a SELECT
statement, which does not limit the number of records selected by the
WHERE clause, but the number of those records that actually are returned
to the client.
There is one subtlety here: If you set a value to the value it currently
has, that is not considered an update, and thus does not count against
the limit.
Note that all aspects of the behavior of LIMIT with UPDATE may easily be
discovered with a little experimentation.
-Mark
- -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
