[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: User variables in update statement
From:       Baron Schwartz <baron () xaprb ! com>
Date:       2007-07-10 19:11:48
Message-ID: 4693D9F4.5090304 () xaprb ! com
[Download RAW message or body]

Hi Scott,

Scott Haneda wrote:
> Is it possible to use user variables in an update statement, I can find only
> scant docs on it, but they do not pertain to what I am trying to do.
> 
> Given this select:
> 
> SELECT user_id, order_status, quantity_chosen, month_price, each_price,
> sales_tax, sales_tax_rate,
> @NEW_each_price:=(each_price + .06) as NEW_each_price,
> @NEW_month_price:=(quantity_chosen * @NEW_each_price) as NEW_month_price,
> @postage_rate:=(0.30) as postage_rate,
> @cost_of_postage:=(quantity_chosen * @postage_rate) as postage,
> @taxable_price:=(@NEW_month_price - @cost_of_postage) as taxable,
> (@taxable_price * sales_tax_rate) as NEW_sales_tax
> 
> As you can see, I am using variables to make some calcs, sure, I can do it
> long hand, but it gets long and confusing.
> 
> Given the above, I end up with some vars like NEW_month_price,
> NEW_each_price etc, I would like to update ... SET price = NEW_each_price,
> but of course, that does not work.
> 
> Suggestions?

It's possible, but I'm not sure it was ever intended.  Still, I have found some very 
useful applications for it.  The trick is to put the assignment inside a function, 
which will a) make sure the assignment happens b) present the assignment as an 
expression that can go on the right-hand-side of a SET clause.

I explained in detail how it works here:
http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

But only read that if you want the background, which is a little tangential for your 
purposes.  Read this instead:

update tbl set
    -- Set each_price to its present value, while setting @NEW_each_price
    each_price = greatest(least(0, @NEW_each_price := each_price + .06)),
    price = @NEW_each_price,
    ....

You can see some complex examples of that technique in this article:
http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html?page=3

Following the examples in that article, you can do a lot more flexible things than I 
just demonstrated.

-- 
Baron Schwartz
http://www.xaprb.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]