What is wrong with this SQL syntax?

Question

UPDATE files 
SET filepath = REPLACE(filepath, `sites/somedomain.com/files/`, `sites/someotherdomain.com/files/`);

I have a table called files with a field called filepath. MySQL returns this error: Unknown column 'sites/somedomain.com/files/' in 'field list'

This happens to me just about every time I copy/paste a sql command from email. So annoying. :) — Jeff Schumacher, Jun 01 '10 at 22:52

score 12 · Accepted Answer · answered Jun 01 '10 at 22:50

12

Use normal quotes instead of backquotes: normal quotes identify strings, backquotes identify column names.

answered Jun 01 '10 at 22:50

Matteo Riva

24,728
12
72
104

score 2 · Answer 2 · answered Jun 01 '10 at 22:52

To expand on kemp's answer:

Backquotes or backticks ` are used in MySQL to enclose the names of schema objects (databases, tables, columns, indexes, procedures, ...). They cannot be used to enclose strings. You must use regular single- or double-quotes: ' or " for that.

score 2 · Answer 3 · edited May 23 '17 at 12:04

2

+1 to answers given by @kemp and @Hammerite.

See also my answer to this question: Do different databases use different name quote?

edited May 23 '17 at 12:04

Community

1
1

answered Jun 01 '10 at 22:56

Bill Karwin

538,548
86
673
828

+1 this link is worth reading. If you want to ensure cross-database compatibility without having to write DBMS-dependent quoting code or DBMS-specific settings configuration stuff, you have to omit quotes for schema names altogether—which unfortunately also means you have to be very careful naming your tables and columns to avoid reserved words. This is a nasty mess. – bobince Jun 01 '10 at 23:08

What is wrong with this SQL syntax?

3 Answers3