How to alias a field or column in MySQL?

mysql subquery alias
user225269 picture user225269 · May 21, 2011 · Viewed 39.3k times · Source

I'm trying to do something like this. But I get an unknown column error:

SELECT SUM(field1 + field2) AS col1, col1 + field3 AS col3 from core

Basically, I want to just use the alias so that I won't need to perform the operations performed earlier. Is this possible in mysql?

Answer

Ravi Parekh picture Ravi Parekh · May 21, 2011

select @code:= SUM(field1 + field2), @code+1 from abc;

But, please be aware of the following (from the MySQL 5.6 docs):

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

So, use at your own risk.

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
What is the error "Every derived table must have its own alias" in MySQL?

I am running this query on MySQL SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) ); and it is giving this error: Every derived table must have its own alias. What's causing this error?

Read More