List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:April 23 1999 9:54pm
Subject:Re: update a set b=b+19 where b>0 ; does not work properly
View as plain text  

This fact is already mentioned in the manual:

E Known errors and design deficiencies in MySQL
An UPDATE that updates a key with a WHERE on the same key may fail
because the key is used to search for records and will be found
multiple times:

UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;

This will be fixed in newer MySQL versions by not using keys that
contain fields that are going to be updated. Until this fixed you can
use the current workaround:

PDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;

This will work because MySQL will not use index on expressions in the
WHERE clause.

Please check the manual and the mailing list archive yourself next



On Fri, Apr 23, 1999 at 08:43:55PM +0200, patrik@stripped wrote:
> >Description:
> 	Hi!
> 	It seems that the following query caused to process the priority
> 	field again and again until it went out of range...
> 	The priority was in range -94..64 before this query.
> 	mysql> update articles set priority = priority + 19 where priority > 0 ;
> 	Query OK, 116100 rows affected (41 min 1.73 sec)
> 	Rows matched: 137616  Changed: 116100  Warnings: 42287

Attachment: [application/pgp-signature]
update a set b=b+19 where b>0 ; does not work properlyPatrik Rak23 Apr
  • Re: update a set b=b+19 where b>0 ; does not work properlyBenjamin Pflugmann24 Apr