“Unknown column in 'field list'”, but colu

Question

DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `transactions` (`purchase_date`) VALUES (NULL)

I've isolated my problem in this code. When I run it, I get the error:

[ERROR in query 3] Unknown column 'purchase_date' in 'field list'

Anyone an idea?

Answer 1

Same error in a different scenario:

This also happens when you miss @ symbol for a variable.

SET @myVar1=1;  SELECT @myVar1;  -- GOOD, correctly prints:  1
SET @myVar1=1;  SELECT  myVar1;  -- BAD, prints:  Unknown column 'myVar1' in 'field list' 

Answer 2

This might not help anyone else, but adding this "just in case" it helps someone.

In my situation it was a different solution.

I receive large datasets as Excel CSV files and use a (WIL) script to convert the .csv file into an importable .sql file. I had an error in my script whereby these two lines did not reference the same table name (I had hard-coded the first location and forgot to update it):

* "INSERT INTO `old_table_name` (`cid`, `date`, etc etc"
* "CREATE TABLE IF NOT EXISTS `":_dbName:"` (etc etc "

I just changed the first line to also get the table name from the variable, and voila!

* "INSERT INTO `":_dbName:"` (`cid`, `date`, etc etc"

So check those two lines in your import SQL file.

Answer 3

This can also happen if you paste a column name when building the table structure. Same error - but the unprintable/invisible characters are in the table structure, not the query.

Answer 4

There is an unprintable character 30 (RecordSeparator) inserted between purchase_date and the ' in the INSERT statement. Just remove the text ('purchase_date') and rewrite it by hand it should be fine.

Answer 5

I just spent the better part of a day figuring this out. My problem was the same: invisible characters kiboshing the query and returning the "unknown column" error.

I solved it by wading back into Windows and removing the garbage using NotePad++.

How did the garbage get in there in the first place? I think it was because I made the mistake of copying some long complex queries into LibreOffice Writer (my functional specs document) instead of just bookmarking them in phpMyAdmin or saving them in a text editor. Pasting them from LibreOffice into the query window is where (I think) the garbage originated.

Once there, it persisted like Malaria. I couldn't even get rid of it by hand-retyping the whole query -- I had to put it into NotePad++ (Encoding menu) and show ANSI and the UTF8 combos and then remove the garbage by hand.

Once that was done, the query worked.

Answer 6

Nery niche solution when I got this error.

I had a BEFORE INSERT trigger on my table that did something with NEW.`field_mysql_doesnt_think_exists` and if I didn't pass that field to an insert statement then I would get

[ERROR in query 3] Unknown column 'field_mysql_doesnt_think_exists' in 'field list'