173

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'
ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
me_here
  • 2,329
  • 5
  • 19
  • 13

14 Answers14

230

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 8 Documentation

Please use double-/single quotes for values, strings, etc. Use backticks for column-names only.

tuergeist
  • 9,171
  • 3
  • 37
  • 58
64

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.

informatik01
  • 16,038
  • 10
  • 74
  • 104
ShoushouLeb
  • 649
  • 5
  • 2
  • 1
    Thank you. I was trying to figure out why my query in PHP was not working, all I had to do was add the single quotes. – RiCHiE Jul 02 '16 at 05:12
  • 3
    I know this is an old post but would you be able to answer why you have to put quotes in? – RiCHiE Jul 02 '16 at 05:13
  • @RiCHiE it's usually safer when using strings. Also, makes more sense to me to put string between quotes. When you use functions like SHA1, for instance, you put quotes in the content inside like `SHA1('$var')` – George Jul 06 '16 at 18:48
  • This worked for me. First I tryed using backticks with no luck. – radbyx Mar 04 '19 at 09:00
  • 1
    Please escape all variables passed into queries properly! Just use `$name = mysqli_real_escape_string($name)` to escape quotes properly! – Le 'nton Mar 28 '19 at 11:01
18

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);
6

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.

Dean Chiu
  • 1,325
  • 1
  • 13
  • 13
  • Thanks. It worked for me. I was also facing this issue and the problem was in trigger. – Shipra Sharma Jul 07 '21 at 06:56
  • Thanks, it was a trigger for me too. After beating my head against the wall for hours, as a sanity check I tried a manual INSERT in Datagrip which gave me the exact same error as what we were getting from integration tests. Turns out there was a typo in the trigger SQL, so that the old trigger wasn't properly dropped leaving us with two competing INSERT triggers – frederj Feb 02 '22 at 19:42
4

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.

Aminah Nuraini
  • 18,120
  • 8
  • 90
  • 108
2

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.

Masterchief
  • 107
  • 3
  • 8
1

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

Poorna
  • 191
  • 1
  • 5
1

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.

user674669
  • 10,681
  • 15
  • 72
  • 105
1

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.

Katia Savina
  • 335
  • 4
  • 13
1

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;
Arya
  • 566
  • 2
  • 9
  • 22
1

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.

constant283
  • 41
  • 5
0

A query like this 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.

Aaron Meese
  • 1,670
  • 3
  • 22
  • 32
hogarth45
  • 3,387
  • 1
  • 22
  • 27
0

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

Suresh
  • 1,491
  • 2
  • 22
  • 27
0

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);
Eternal21
  • 4,190
  • 2
  • 48
  • 63