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