MySQL Error Code 1166. Incorrect column name

Question

When I try to run a routine I get the following error:

Error Code: 1166. Incorrect column name 'School.`School Name` = case when School.`Web School Name` is null then School.`School Name` els'

I have double checked the column name and there is in fact a School.School Name. There are no leading or trailing spaces. Here is the beginning of the routine that the error is referring to:

CREATE temporary TABLE tt_Step1
SELECT DISTINCT State.Code, State.Name, 
School.`School Name` = case 
    when School.`Web School Name` is null then School.`School Name`
    else School.`Web School Name`
    end,
School.`School ID` 
-- Into tt_Step1
FROM State LEFT JOIN School ON State.Code = School.State 
Where (School.`School ID` <> ...

I recently converted this code from MSSQL to MySQL so there may be something I missed but I cannot find any errors. The original MSSQL query runs fine in SQL Server Management Studio but the converted version of it in MySQL does not.

Answer 1

In my case, it was caused by an unseen extra space after the column name. I wrote promotion-ids instead of promotion-id.

Answer 2

Give this a shot:

CREATE temporary TABLE tt_Step1
SELECT DISTINCT State.Code, State.Name, 
coalesce(School.`Web School Name`, School.`School Name`),
School.`School ID` 
-- Into tt_Step1
FROM State LEFT JOIN School ON State.Code = School.State 
Where (School.`School ID` <> ...