What should I do if there is an unknown column in field list?

4.6K Asked by alexDuncan in SQL Server , Asked on Oct 3, 2022

rror: Code 1054. Unknown column 'U2.id_naslov' in 'field list' gets thrown on this simple query in MySQL Workbench:

UPDATE krneki_1 AS U1, krneki_2 AS U2 
SET U1.id_naslov = U2.id_naslov
WHERE (U2.id_zaposlen = U1.id_naslovi_zaposleni)

I have searched and read other posts on the net but nothing helps...

I guess it's a trivial solution but I just can't see it.

This kind of error never came up on TSQL (sql server).

Table krneki_2 was created by Mysql workbench via data import (create new table) later on when this error occurred I also altered the number fields to smallint just to see if it helps... but... nothing.

Result of SHOW CREATE TABLE krneki_2:

       Table: krneki_2 
Create Table: CREATE TABLE `krneki_2` 
( `id` smallint(6) NOT NULL AUTO_INCREMENT, 
  `id_naslov` smallint(6) NOT NULL, 
  `id_zaposlen` smallint(6) NOT NULL, 
  PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)
Result of SHOW CREATE TABLE krneki_1:
       Table: krneki_1
Create Table: CREATE TABLE `krneki_1` (
  `id_naslovi_zaposleni` smallint(6) NOT NULL AUTO_INCREMENT,
  `id_naslov` smallint(6) DEFAULT NULL,
  `id_zaposleni` smallint(6) DEFAULT NULL,
  `id_aktiven` tinyint(4) DEFAULT '0',
  `cas_vnosa` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_naslovi_zaposleni`)
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Results from information_schema, specifically from this query suggested in comments:
select
  table_catalog, table_schema, table_name, column_name, ordinal_position
from information_schema.columns
where table_name like '%krneki_1%' and column_name like '%naslov%' ;
Results for krneki_1 and naslov:
+---------------+--------------+-------------+----------------------+------------------+
| table_catalog | table_schema | table_name  | column_name          | ordinal_position |
+---------------+--------------+-------------+----------------------+------------------+
| def           | hq_db        | krneki_1    | id_naslovi_zaposleni |                1 |
| def           | hq_db        | krneki_1    | id_naslov            |                2 |
+---------------+--------------+-------------+----------------------+------------------+
2 rows in set (0.00 sec)
Results for krneki_2 and naslov:
+---------------+--------------+-------------+--------------+------------------+
| table_catalog | table_schema | table_name  | column_name  | ordinal_position |
+---------------+--------------+-------------+--------------+------------------+
| def           | hq_db        | krneki_2    | id_naslov    |                2 |
+---------------+--------------+-------------+--------------+------------------+
1 row in set (0.00 sec)
Results for krneki_2 and zaposlen:
+---------------+--------------+-------------+--------------+------------------+
| table_catalog | table_schema | table_name  | column_name  | ordinal_position |
+---------------+--------------+-------------+--------------+------------------+
| def           | hq_db        | krneki_2    | id_zaposlen  |                3 |
+---------------+--------------+-------------+--------------+------------------+
1 row in set (0.00 sec)
Further digging, as suggested:
select 
  table_catalog, table_schema, table_name, column_name, ordinal_position,         
  char_length(column_name) as cl, length(column_name) as l
from information_schema.columns 
where table_name = 'krneki_2' ;
Results for krneki_2
Results for krneki_2:
+-------------+------------+----------+-----------+----------------+---+---+-------------+
|table_catalog|table_schema|table_name|column_name|ordinal_position| cl| l | column_type |
+-------------+------------+----------+-----------+----------------+---+---+-------------+
| def         | hq_db      | krneki_2 |id         |              1 |  2|  2| smallint(6) |
| def         | hq_db      | krneki_2 |id_naslov  |              2 | 10| 12| smallint(6) |
| def         | hq_db      | krneki_2 |id_zaposlen|              3 | 11| 11| smallint(6) |
+-------------+------------+----------+-----------+----------------+---+---+-------------+
3 rows in set (0.00 sec)
Results for krneki_1:
+-------------+------------+----------+--------------------+----------------+--+--+-----------+
|table_catalog|table_schema|table_name| column_name        |ordinal_position|cl| l|column_type|
+-------------+------------+----------+--------------------+----------------+--+--+-----------+
| def         | hq_db      | krneki_1 |id_naslovi_zaposleni|              1 |20|20|smallint(6)|
| def         | hq_db      | krneki_1 |id_naslov           |              2 | 9| 9|smallint(6)|
| def         | hq_db      | krneki_1 |id_zaposleni        |              3 |12|12|smallint(6)|
| def         | hq_db      | krneki_1 |id_aktiven          |              4 |10|10|tinyint(4) |
| def         | hq_db      | krneki_1 |cas_vnosa           |              5 | 9| 9|datetime   |
+-------------+------------+----------+--------------------+----------------+--+--+-----------+
5 rows in set (0.00 sec)
krneki_2 with HEX:
+-------------+------------+----------+-----------+----------------+--+--+-------------------------+
|table_catalog|table_schema|table_name|column_name|ordinal_position|cl|l | hex                     |      
+-------------+------------+----------+-----------+----------------+--+--+-------------------------+ 
| def         | hq_db      | krneki_2 |id         |              1 | 2| 2|6964                     |   
| def         | hq_db      | krneki_2 |id_naslov  |              2 |10|12|EFBBBF69645F6E61736C6F76 |   
| def         | hq_db      | krneki_2 |id_zaposlen|              3 |11|11|69645F7A61706F736C656E   |  
+-------------+------------+----------+-----------+----------------+--+--+-------------------------+   
3 rows in set (0.00 sec)  
krneki_1 with HEX:
+-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+
|table_catalog|table_schema|table_name|column_name         |ordinal_position|cl| l|hex                                     |              
+-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+
| def         | hq_db      | krneki_1 |id_naslovi_zaposleni|              1 |20|20|69645F6E61736C6F76695F7A61706F736C656E69|
| def         | hq_db      | krneki_1 |id_naslov           |              2 | 9| 9|69645F6E61736C6F76                      |
| def         | hq_db      | krneki_1 |id_zaposleni        |              3 |12|12|69645F7A61706F736C656E69                |
| def         | hq_db      | krneki_1 |id_aktiven          |              4 |10|10|69645F616B746976656E                    |
| def         | hq_db      | krneki_1 |cas_vnosa           |              5 | 9| 9|6361735F766E6F7361                      |
+-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+
5 rows in set (0.00 sec)

Answered by Amanda Hawes

The problem - unknown column in field list

may be that you had forgotten to execute a use ; statement, and consequently weren't executing the insert in the correct database.

In other words, you might be trying to insert into a table that exists in a different database, but with a different structure.

Your Answer

Answer (1)

Ranjana

Encountering an "unknown column in field list" error in SQL indicates that you are trying to reference a column that does not exist in the table you are querying. Here are steps to troubleshoot and resolve this issue:

Check Column Names:

Verify that the column names used in your SQL query are correct. Ensure there are no typos or incorrect column names.

Review Table Structure:

Use the DESCRIBE or SHOW COLUMNS command to review the structure of the table and confirm the columns available.

  DESCRIBE table_name;SHOW COLUMNS FROM table_name;

Verify Aliases:

If you are using table aliases, make sure they are correctly referenced in your query.

  SELECT t.column_name FROM table_name AS t;

Check SQL Query:

Ensure that your SQL query is correctly formatted. For example, make sure you are not mistakenly using a reserved keyword or misspelling the column or table name.

Database Consistency:

Ensure the database schema is up to date. If you have recently altered the table, ensure all instances and replicas of the database are consistent.

Recreate Indexes and Views:

If the column is part of an index or a view, and you've recently modified the schema, you might need to recreate the index or view.

Clear Cache:Some database systems might cache the schema information. Clearing the cache or restarting the database server might help.

Here is a checklist to follow:

Example Query:

  SELECT column_name FROM table_name;

Check for Typos:

  SELECT cloumn_name FROM table_name;  -- Typo in column_name

Review Table Structure:

  DESCRIBE table_name;SHOW COLUMNS FROM table_name;

Using Aliases Correctly:

  SELECT t.column_name FROM table_name AS t;

Query with Correct Syntax:

  SELECT column_name FROM table_name;

If you still encounter issues after following these steps, please provide the specific SQL query and error message you are receiving for more tailored assistance.

5 Months