At 6:54 PM -0700 10/11/01, David Howlett wrote:
>The section of the manual dealing with LOCK/UNLOCK gives the following
>example:
>
>mysql> LOCK TABLES trans READ, customer WRITE;
>mysql> select sum(value) from trans where customer_id= some_id;
>mysql> update customer set total_value=sum_from_previous_statement
> where customer_id=some_id;
>mysql> UNLOCK TABLES;
>
>What is sum_from_previous statement?
It means that you run the LOCK TABLES statement, then the SELECT.
You look at the output from the SELECT to see what the sum is.
Then you substitute that value into the UPDATE statement where it
says sum_from_previous_statement. That works okay if you're issuing
the commands manually and can see what the SELECT returns. But now
that MySQL has SQL variables, you can assign the sum to a variable
and refer to it in the UPDATE statement:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select @sum := sum(value) from trans where customer_id= some_id;
mysql> update customer set total_value=@sum
where customer_id=some_id;
mysql> UNLOCK TABLES;
I would like to use max(value) and incriment it. Would this be written as:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select max(value)+1 from trans where customer_id= some_id;
mysql> update customer set total_value=max_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;
>
>I would like to use max(value) and incriment it. Would this be written as:
>
>mysql> LOCK TABLES trans READ, customer WRITE;
>mysql> select max(value)+1 from trans where customer_id= some_id;
>mysql> update customer set total_value=max_from_previous_statement
> where customer_id=some_id;
>mysql> UNLOCK TABLES;
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select @max := max(value)+1 from trans where customer_id= some_id;
mysql> update customer set total_value=@max
where customer_id=some_id;
mysql> UNLOCK TABLES;
or else
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select @max := max(value) from trans where customer_id= some_id;
mysql> update customer set total_value=@max+1
where customer_id=some_id;
mysql> UNLOCK TABLES;
--
Paul DuBois, paul@stripped
