Unknown column in 'field list' error on MySQL Update query

user_92069 0
Viewed 999685

I keep getting MySQL error #1054, when trying to perform this update query:

UPDATE MASTER_USER_PROFILE, TRAN_USER_BRANCH
SET MASTER_USER_PROFILE.fellow=`y`
WHERE MASTER_USER_PROFILE.USER_ID = TRAN_USER_BRANCH.USER_ID
AND TRAN_USER_BRANCH.BRANCH_ID = 17

It's probably some syntax error, but I've tried using an inner join instead and other alterations, but I keep getting the same message:

Unknown column 'y' in 'field list'
mysql-error-1054 mysql sql
14 Answers

Enclose any string to be passed to the MySQL server inside single quotes, e.g.:

$name = "my name"
$query = " INSERT INTO mytable VALUES ( 1 , '$name') "

Note that although the query is enclosed between double quotes, you must enclose any string in single quotes.

user_2401390

Just sharing my experience on this. I was having this same issue. The insert or update statement is correct. And I also checked the encoding. The column does exist. Then! I found out that I was referencing the column in my Trigger. You should also check your trigger see if any script is referencing the column you are having the problem with.

user_33959130

In my case, it was caused by an unseen trailing space at the end of the column name. Just check if you really use "y" or "y " instead.

user_15646590

Just sharing my experience on this. I was having this same issue. My query was like:

select table1.column2 from table1

However, table1 did not have column2 column.

user_6746690

If it is hibernate and JPA. check your referred table name and columns might be a mismatch

user_78226630

In my case, the Hibernate was looking for columns in a snake case, like create_date, while the columns in the DB were in the camel case, e.g., createDate. Adding

spring:
  jpa:
    hibernate:
      naming: # must tell spring/jpa/hibernate to use the column names as specified, not snake case
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl

to the application.ymlhelped fix the problem.

user_52793450

In my case, I used a custom table alias for the FROM table, but I used the default table alias (MyTable) in the field list instead of the custom table alias (t1). For example, I needed to change this...

mysql> SELECT MyTable.`id` FROM `MyTable` t1;

...to this...

mysql> SELECT t1.`id` FROM `MyTable` t1;
user_29544290

I too got the same error, problem in my case is I included the column name in GROUP BY clause and it caused this error. So removed the column from GROUP BY clause and it worked!!!

user_30490360

I got this error when using GroupBy via LINQ on a MySQL database. The problem was that the anonymous object property that was being used by GroupBy did not match the database column name. Fixed by renaming anonymous property name to match the column name.

.Select(f => new 
{
   ThisPropertyNameNeedsToMatchYourColumnName = f.SomeName
})
.GroupBy(t => t.ThisPropertyNameNeedsToMatchYourColumnName);
user_12884730

In my case I had misspelled the column name in the table's trigger. Took me a while to connect the error message with the cause of it.

user_29963070
Related