CODEWITHSUNDEEP
mysql
ck_
ck_

Reputation: 3403

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

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!

Upvotes: 8

Views: 4883

Answers (3)

longneck
longneck

Reputation: 12226

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

Upvotes: 7

knittl
knittl

Reputation: 265221

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

Upvotes: 0

longneck
longneck

Reputation: 12226

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

Upvotes: 4

Related Questions