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  
Hi!

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
time.

Bye,

	Benjamin.


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]
Thread
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