List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 11 1999 2:14pm
Subject:Re: UPDATE causing problems
View as plain text  
Graham Ashton wrote:
> 
> I've got a relatively small table like this;
> 
> mysql> SELECT * FROM table LIMIT 3;
> +------+------+------+---------+
> | col1 | col2 | col3 | counter |
> +------+------+------+---------+
> |    1 |    0 |    1 |       0 |
> |    1 |    1 |    1 |       0 |
> |    1 |    2 |    1 |       0 |
> +------+------+------+---------+
> 
> The key is defined by
> 
>   UNIQUE (col1, col2, col3, counter)
> 
> All the columns are defined as INT.
> 
> What I want to do is to update the contents of the "counter" column
> based on the values of the other columns.
> 
> I've been trying the following
> 
>   UPDATE table
>      SET counter = counter + 1
>    WHERE col1 = 1 AND col2 = 1 AND col3 = 1
> 
> but whenever I try it the mysqld CPU load goes to 100%, and it doesn't
> appear to come back. If I kill mysqld then things return to normal, but
> it appears as though my UPDATE statement went into an indefinite loop
> (the value in the "counter" column is enormous). It appears to be the
> "counter = counter + 1" statement that is causing the problem.
> 
> This works fine, however;
> 
>   UPDATE table
>      SET counter = counter + 1
>    WHERE col2 = 1
> 
> It appears as though I don't understand how the UPDATE statement
> actually does it's stuff. Can anybody point out which tree I ought to be
> barking up? In the meantime I'm going to revert to SELECTing the value
> of the counter I'm interested in, adding 1 to it myself, and then doing
> a simple UPDATE.
> 
> Any pointers would be much appreciated.
> 
> P.S. I'm using 3.21.33 on Debian 2.1.
> 
> --
> Graham

Hi Graham

Your problem comes from the fact, that you update a column which is part of a KEY which is
used in the WHERE clause.
To make this work, you have to stop using that key.
Try this:
UPDATE
	table
SET
	counter = counter + 1
WHERE
	col1 + 0 = 1
	AND col2 = 1
	AND col3 = 1

Tschau
Christian

Thread
UPDATE causing problemsGraham Ashton11 May
  • Re: UPDATE causing problemsChristian Mack11 May
    • Re: UPDATE causing problemsGraham Ashton11 May
  • Re: UPDATE causing problemsFred Read11 May