Can I re-use an expression in a MySQL query as a variable for another field?

Question

Is there any workaround so I can actually do something like this without having to repeat the entire expression or force a UNION or temporary table?

SELECT  (complex expression) AS variable1,
        (complex expression based on variable1) AS variable2

Since variable1 is not defined and available to the 2nd item because of how mysql works, the above concept can never work.

I either have to repeat the expression for variable2, or use a UNION or a temporary table and use two passes.

Is there some trick that I am not aware of to accomplish this more efficiently?

(note that I need to know the answer for both variable1 and variable2 as they are then used for an INSERT)

Thanks for any ideas!

Answer 1

SELECT @v1:=(complex expression) AS variable1,
       (complex expression * @v1) AS variable2

Answer 2

i think the only way is to repeat your first complex_expression in your second one, although i thought mysql could handle such cases.

edit: a quick search turned up this: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Answer 3

push the first calculation in to a derived table:

select variable1
     , complex_function(variable1, other_column) as variable2
     , yet_another column
  from (select complex_operation as variable1
             , other_column
             , yet_another_column
          from whatever) dt