CODEWITHSUNDEEP
mysqlsqlmysql-5.7
ankit.vishen
ankit.vishen

Reputation: 1170

mysql update ERROR 1054 : Unknown column 'x' in 'field list'

I was trying to update mysql table and got ERROR 1054 and after that something strange happen.

Table schema

CREATE TABLE `useraccount` (
  `userId` bigint(20) NOT NULL,
  `currentBalance` float NOT NULL,
  `currentDataBalance` bigint(20) NOT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I had one entry in my table i.e.

mysql> select * from UserAccount;
+--------+----------------+--------------------+
| userId | currentBalance | currentDataBalance |
+--------+----------------+--------------------+
|      1 |              0 |               4296 |
+--------+----------------+--------------------+

I tried to update currentDataBalance field and got the error

ERROR 1054 (42S22): Unknown column '253600l' in 'field list'

mysql> update UserAccount set currentDataBalance=253600l where userId=1;
ERROR 1054 (42S22): Unknown column '253600l' in 'field list'

Then I removed the last digit of update value (from 253600l to 253600) and value got updated

mysql> update UserAccount set currentDataBalance=253600 where userId=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Then again I changed the value to previous one (from 253600 to 2536001) and value got updated this time.

mysql> update UserAccount set currentDataBalance=2536001 where userId=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I go through many posts on stackoverflow which are related to error 1054 but I did't get relevant answer.

Upvotes: 1

Views: 9907

Answers (2)

bakatrouble
bakatrouble

Reputation: 1844

You have small latin character "L" instead of number "1" in your update query. As value you want to set looks like an identifier to MySQL, it tries to lookup column with such name, and can't find it, that's why you're getting this error.

Upvotes: 0

Thomas G
Thomas G

Reputation: 10226

This query 

update UserAccount set currentDataBalance=253600l where userId=1;

returns this error :

ERROR 1054 (42S22): Unknown column '253600l' in 'field list'

because you have a letter in your "number" : 253600l, which turns your number into a string.Since this string is not surrounded with quotes, MySQL assumes that it is the name of an object in the database, a column in this case. The object doesnt exists, thus this specific error.

Upvotes: 1

Related Questions