Unknown column in field list MySQL

Question

I have a table containing mainly numbers with 1 row of column names at the top.

I ran the following query:

select * , (High - Low) as DiffHL from exdata3; 

(exdata3 is table name and High, Low are existing columns. DiffHL is new column which I have mentioned first time in this query itself)

The query ran fine and added the new column DiffHL with the corresponding calculated values.

Now when I run another query after this

select * from exdata3 where DiffHL >100 

I get the following error:

error (1054) (42S22)Unknown column DiffHL in 'where clause'

I try to do any query with DiffHL it gives me the same above error. Where have I gone wrong. Do I need to create DiffHL first before putting/assigning values in it? New to MySQL so bear with me.

Answer 1

Actually, your first SELECT statement has not created column on table:

SELECT *, (High - Low) as DiffHL FROM exdata3;

Instead, it created a "virtual" column only accessible within it's resultset.

You should add column manually, or rewrite your SELECT as such:

SELECT *, (High - Low) as DiffHL FROM exdata3 WHERE (High - Low) > 100;

If you really want to add column DiffHL to the table, you may try to add column with following statements:

ALTER TABLE exdata3 ADD COLUMN DiffHL INT NULL;
-- Create an empty DiffHL column

UPDATE exdata3 SET DiffHL = High - Low;
-- Fill DiffHL column with values

SELECT * FROM exdata3;
-- Here DiffHL column is actually exists

Answer 2

MySql only temporarily added that column for display with that query

Answer 3

It's not possible to reference (in the WHERE clause) any expression in the SELECT list by the alias assigned to the expression (within a single SELECT, without an inline view).

It is possible to reference the expression by it's alias in a HAVING clause.

For example, this would be valid:

SELECT t.*
     , (t.High - t.Low) AS DiffHL
  FROM exdata3 t
HAVING DiffHL > 100
^^^^^^

In the WHERE clause, you would need to repeat the expression. For example, this would be valid:

SELECT t.*
     , (t.High - t.Low) AS DiffHL
  FROM exdata3 t
 WHERE (t.High - t.Low) > 100 
       ^^^^^^^^^^^^^^^^

Answer 4

you have misunderstood something: DiffHL is just the name of your field in your resultset. MySQL doesn't create the field, it just names the column in your resultset.

Answer 5

The column does not exists in your table.

select * , (High - Low) as DiffHL from exdata3;

This query only creates an alias for the column (High - Low)

Use

select * from exdata3 where (High - Low) >100 

Answer 6

Alias name u ran in first time query, you have to define like this. Can you try it?

select * from exdata3 where DiffHL >100 

to

select * from exdata3 where (High - Low) > 100

Answer 7

select * , (High - Low) as DiffHL from exdata3

In above query you are aliasing (High - Low) to DiffHL it will not add the new column to your existing table.

Try below correct query:

select * , (High - Low) as DiffHL from exdata3 where DiffHL >100