SQL Query Using Primary Key

Question

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?

Answer 1

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