MySQL Syntax with DELETE FROM?

Question

I have this bit of SQL that always returns an error, though I can't find why it is returning the error. I have connected to the database with no errors. I'm running PHP 5.2.17, MySQL 5.5.25a, and Apache 2.4.2.

The SQL:

DELETE FROM mail WHERE to=1

The error:

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 'to=1' at line 1

Dupe of [Yii Framework/PDO getting error CDbCommand failed to execute the SQL statement: SQLSTATE\[42000\]](http://stackoverflow.com/q/2316616/), [SQLSTATE\[42000\]: Syntax error or access violation: 1064 You have an error in your SQL syntax](http://stackoverflow.com/q/4544051/) and others. — outis, Jul 17 '12 at 02:03

score 6 · Accepted Answer · edited Jul 17 '12 at 01:59

6

TO is a reserved word, you need to use backticks:

 DELETE FROM mail WHERE `to` = 1

edited Jul 17 '12 at 01:59

Wiseguy

20,522
8
65
81

answered Jul 17 '12 at 01:59

Wesley Murch

101,186
37
194
228

you should be able to see it as it is highlighted :) – Cole Tobin Jul 17 '12 at 02:01
Thank you, that worked. I didn't know it was reserved xD I was running it from a PHP file, so the query was enclosed in quotations. – DerekAthley Jul 17 '12 at 02:04
It's a good habit to use backticks anyways, and quote your values (which I didn't do in the example). The query actually isn't executed with quotations, that's just PHP syntax. You may click the checkmark to the left to mark your question as resolved if you wish. – Wesley Murch Jul 17 '12 at 02:09

score 0 · Answer 2 · answered Jul 17 '12 at 02:24

0

By adding backticks on the column name escapes in from MySQL Reserved Word

DELETE FROM mail WHERE `to`=1

answered Jul 17 '12 at 02:24

Ambrose

501
3
13

score -1 · Answer 3 · answered Jul 17 '12 at 01:58

-1

if the column to is not e.g. INT or DEC you should make it to = "1"

answered Jul 17 '12 at 01:58

NewInTheBusiness

1,465
1
9
14

MySQL Syntax with DELETE FROM?

3 Answers3