. Advertisement .
..3..
. Advertisement .
..4..
Lately, we have received a lot of questions asking about the MySQL Error #1054, an unknown column in field list.
Many complain that there is something wrong with their database. Such kinds of errors happen continuously whenever they try to update or insert any particular table.
There are actually reasons for this strangeness in making a scene. Scroll down to learn more about causes as well as how to get a cure!
What Is The Mysql Error #1054?
Such a failure takes place when MySQL cannot find a column name in your SQL script.
Let’s say you have a table called students that includes the resulting information:
+—-+———+———+——-+——–+
| id | name | subject | score | gender |
+—-+———+———+——-+——–+
| 1 | Thomas | Biology | 5 | male |
| 2 | Sara | Biology | 4 | female |
| 3 | Hans | Math | 8 | male |
+—-+———+———+——-+——–+
You will know this error occurs when attempting to insert a column that is not present into the table and seeing the codes running the saying as follow:
INSERT INTO students(student_name) VALUES (Claire);
— ERROR 1054 (42S22): Unknown column ‘student_name’ in ‘field list’
Unknown Column In Field List – Probable Causes
There are three possible explanations for this matter.
#1. Non-existing column
The first and foremost is obviously because the value column you enter does not exist in the table. Take the code above as an example. There is no “Claire” value in the students’ table.
#2. Quotation mark
The second potential reason is perhaps you didn’t employ the quotation marks for the string values. For instance, it won’t help if you just type Claire rather than “Claire”. Check out this code below to learn more.
INSERT INTO students(name) VALUES (Claire);
— ERROR 1054 (42S22): Unknown column ‘Claire’ in ‘field list’
That way, you must enclose the Claire value in “” or “. Otherwise, MySQL will assume you’re aiming to insert data from another column into the target column.
#3. The @ Sign
Another possibility is that you can get the error by invoking a variable without the @ sign, as illustrated below:
SET @myVar=”Hi”;
SELECT myVar;
— ERROR 1054 (42S22): Unknown column ‘myVar’ in ‘field list’
#4. Backticks
You’ve utilized the quotation marks (double or single), but the code doesn’t get any better? Then the case could be that you’ve mistaken them with backticks.
For example, we have:
UPDATE students SET subject = `English` WHERE id = 1;
— ERROR 1054 (42S22): Unknown column ‘English’ in ‘field list’
#5. Faulty FROM clause
The issue might occur if the FROM clause and the SELECT clause do not match each other as seen below:
SELECT students.name FROM cities;
— ERROR 1054 (42S22): Unknown column ‘students.name’ in ‘field list’
#6. Invisible Characters
This situation likely presents itself when you copy the script from an unreliable source. As such, there might be some hidden characters causing the program to report the failure. The only method to avoid it is to manually rewrite the phrases.
Conclusion
If you get an error saying that a column doesn’t exist in the database when it actually does, don’t rush to withdraw anyway. There is a likelihood that the culprit of this unknown column in field list error might be one of those above.
Hopefully, this instruction has provided you with some pointers on how to correct your code the best way. Wish you lots of luck!
Leave a comment