CODEWITHSUNDEEP
mysql
Bene
Bene

Reputation: 1281

MYSQL INSERT INTO syntax error

I would like to insert a new row in the following table:

+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| index             | bigint(20) unsigned | NO   | PRI | NULL    |       |
| exports_fields_id | bigint(20) unsigned | NO   | PRI | NULL    |       |
| exports_id        | bigint(20) unsigned | NO   | MUL | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+

Therefor I tried the following sql statement:

INSERT INTO 'exports_has_export_fields' ('index', 'exports_fields_id', 'exports_id') VALUES (0, 78, 3);

But then I get the following error:

ERROR 1064 (42000): 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 ''exports_has_export_fields' ('index', 'exports_fields_id', 'exports_id') VALUES ' at line 1

Upvotes: 1

Views: 1396

Answers (5)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6726

index is a reserved word. Quote it (with backticks).

So you should use backticks quotes ` instead of single quotes '.

INSERT INTO `exports_has_export_fields` (`index`, `exports_fields_id`, `exports_id`) VALUES (0, 78, 3);

Upvotes: 1

Daniel Hernandez
Daniel Hernandez

Reputation: 229

Have you tried removing the single quotes from your table name and fields?

INSERT INTO exports_has_export_fields (`index`, exports_fields_id, exports_id) VALUES (0, 78, 3);

Or if you want to shorten your query, try

INSERT INTO exports_has_export_fields VALUES (0, 78, 3);

Upvotes: 1

fedorqui
fedorqui

Reputation: 289725

You are using single quotes, but you need backticks (`) to refer to fields or tables whose name may conflict with MySQL reserved keywords.

In this case, just index is a reserved one, even though you can backtick everything for safety sake.

So you can say:

INSERT INTO `exports_has_export_fields` (`index`, `exports_fields_id`, `exports_id`) VALUES (0, 78, 3);
            ^                         ^  ^     ^  ^                 ^  ^          ^

or just

INSERT INTO exports_has_export_fields (`index`, exports_fields_id, exports_id) VALUES (0, 78, 3);
                                       ^     ^

From When to use single quotes, double quotes, and backticks?:

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

Upvotes: 3

Ni Nö
Ni Nö

Reputation: 51

Try it with (edit with backticks)

INSERT INTO `exports_has_export_fields` (`index`, `exports_fields_id`, `exports_id`) VALUES (0, 78, 3);

There shouldn't be single quotes on table and column names.

If you are inserting in all Columns of a Table you can shorten your Insert statement like this:

INSERT INTO exports_has_export_fields VALUES (0, 78, 3);

Upvotes: 1

hp2p
hp2p

Reputation: 11

I think field index is primary key and it not zero try

SET sql_mode='NO_AUTO_VALUE_ON_ZERO';

before inserting the 0 value.

Upvotes: 1

Related Questions