CODEWITHSUNDEEP
sqlindexingkeysql-update
user1028882
user1028882

Reputation: 53

SQL Query Using Primary Key

I have a SQL table with three columns (key, id, loglevel). The key column is set as the primary key, and is auto-incremented.

Imagine this: 

key   id   loglevel
1     223     5
2     445     8

Now I want to update the table by selecting the row thata corresponds to a specific value of "key".

I am using the line:

mysql_query("UPDATE Logs SET loglevel = 4 WHERE key = 2;");

However this doesn't work.

When I change it to 

mysql_query("UPDATE Logs SET loglevel = 4 WHERE id = 445;");

it works fine. However, I want to update based on "key" and not "id".

Any ideas what I'm doing wrong?

Upvotes: 3

Views: 743

Answers (2)

Mike
Mike

Reputation: 318

"key" is a reserved word in MySQL (see http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html). I would suggest using a word other then 'key' as your Primary Key as it could be confusing later to have a column named 'key' and another named 'id' as a common convention is to use 'id' as the Primary Key...

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 839044

In MySQL key is a reserved word and must be quoted.

"UPDATE Logs SET loglevel = 4 WHERE `key` = 2"

I'd also strongly recommend that you look at the value of mysql_error when your query fails as this may have given you the hint you needed to solve this yourself.

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key' at line 1

Upvotes: 6

Related Questions