mysql update query error Code 1054 unknown column in field list

Error: 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 |
+---------------+--------------+-------------+----------------------+--------

The error message is quite clear. The table krneki_2 does not have a column named id_naslov. Unless there is some corruption in system tables or a bug, there is no question about that.

So we have to eliminate several possibilities why this appears:

-
There are mismatches between the CREATE TABLE statements and the UPDATE:

CREATE TABLE ` krneki_1` ...

CREATE TABLE ` krneki_2` ...

UPDATE krneki_1 AS U1, krneki_2 AS U2 ...

The mismatch is a space at the start of the names.

This should have given an error of "Table 'krneki_1' doesn't exist" so my educated guess is that you have two versions of the table krneki_1, and the version without the space doesn't have the id_naslov column.

We eliminated this possibility, it was a copy paste error from the OP.

-
The column name in the CREATE TABLE and the UPDATE are not identical. They may look the same but there may be unprintable characters or they may have Unicode characters that look the same but are different code points. To find out, we can use this query:

select 
  table_catalog, table_schema, table_name, column_name, ordinal_position,
  char_length(column_name) as cl, length(column_name) as l,
  hex(column_name) as hex
from information_schema.columns 
where table_name = 'krneki_2' ;

which reveals the difference (not needed columns removed from the output):

+------------+-------------+------------------+----+----+
| table_name | column_name | ordinal_position | cl | l  |
+------------+-------------+------------------+----+----+
| krneki_2   | id          |                1 |  2 |  2 |
| krneki_2   | id_naslov   |                2 | 10 | 12 |   -- !!! --
| krneki_2   | id_zaposlen |                3 | 11 | 11 |     
+------------+-------------+------------------+----+----+

Note 12 is bigger than 10 and there is the problem! It means that the column name has 10 characters and uses 12 bytes. These numbers should both be 9 (if we count id_naslov correctly and if all the 9 characters were ASCII), so something fishy is going on there.

You can add hex(column_name) in the select list of this last query and we'll know what exactly the column name is. Then you can alter it to have only printable ascii characters.

To fix, you need something like this:

set @column := X'EFBBBF69645F6E61736C6F76' ;  
set @qry:= concat('alter table krneki_2 change column ', 
                  @column, 
                  ' id_naslov smallint') ;
prepare stmt from @qry ;
execute stmt ;

StackExchange Database Administrators Q#162900, answer score: 8