List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 12 2001 2:47am
Subject:Re: sum from previous statement
View as plain text  
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
Thread
sum from previous statementDavid Howlett12 Oct
  • Re: sum from previous statementPaul DuBois12 Oct