Unknown column in 'field list' error on My

Question

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' 

Answer 1

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!!!

Answer 2

You might check your choice of quotes (use double-/ single quotes for values, strings, etc and backticks for column-names).

Since you only want to update the table master_user_profile I'd recommend a nested query:

UPDATE
   master_user_profile
SET
   master_user_profile.fellow = 'y'
WHERE
   master_user_profile.user_id IN (
      SELECT tran_user_branch.user_id
      FROM tran_user_branch WHERE tran_user_branch.branch_id = 17);

Answer 3

A query like thi will also cause the error

select table1.id from table2

Where the table is specified in column select and not included in the from clause.

Answer 4

While working on a .Net app build with EF code first, I got this error message when trying to apply my migration where I had a Sql("UPDATE tableName SET columnName = value"); statement.

Turns out I misspelled the columnName.

Answer 5

Try using different quotes for "y" as the identifier quote character is the backtick (“`”). Otherwise MySQL "thinks" that you point to a column named "y".

See also MySQL 5 Documentation

Answer 6

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.