1

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

Bene
  • 1,251
  • 6
  • 19
  • 34

5 Answers5

3

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.

Community
  • 1
  • 1
fedorqui
  • 275,237
  • 103
  • 548
  • 598
1

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);
Ni Nö
  • 51
  • 7
  • Thanks for the answer with the shorter statement. The one above fails aswell. Only the shorten statement insert a new row. You know with it fails? – Bene Aug 20 '15 at 10:08
  • @user3205343 : try it with the backticks as mentioned in the answers above. – Ni Nö Aug 20 '15 at 10:12
  • this doesn't work because `index` is a reserved keyword in MySQL. – fedorqui Aug 20 '15 at 10:13
1

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);
Daniel Hernandez
  • 229
  • 1
  • 8
1

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);
Valery Viktorovsky
  • 6,487
  • 3
  • 39
  • 47
1

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.

hp2p
  • 11
  • 1