Travis P Travis P - 1 month ago 8
MySQL Question

MySQL Error Code 1166. Incorrect column name

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

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` <> ...