Mysql insert statement syntax error with SET

Question

INSERT INTO salon_client_prepaidCard SET (\'010818-demo-1\', 4, 5, 1, 6000)

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 \'(\'010818-demo-1\', 4, 5, 1, 6000)\' at line 1', sqlState: '42000', index: 0, sql:

The table has an id field that is auto-increment. What looks wrong here?

I am using SET, not values. It works fine most other times and is valid syntax — systemdebt
– systemdebt, Commented Aug 1, 2018 at 13:51

Clint · Accepted Answer · 2018-08-01 15:01:11Z

4

the correct syntax for mysql inserts is;-

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

or using set;-

INSERT INTO table_name
   SET column1 = 'value1',
       column2 = 'value2',
       column3 = 'value3';

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query;-

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

edited Aug 1, 2018 at 15:01

answered Aug 1, 2018 at 13:51

Clint

1,0738 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BenM · Accepted Answer · 2018-08-01 13:50:48Z

1

Why are you escaping quotes? Also, VALUES should be used instead of SET.

INSERT INTO salon_client_prepaidCard VALUES ('010818-demo-1', 4, 5, 1, 6000);

edited Aug 1, 2018 at 13:50

answered Aug 1, 2018 at 13:49

BenM

53.3k26 gold badges116 silver badges172 bronze badges

6 Comments

systemdebt Over a year ago

Still does not work. I get 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 '('010818-demo-1', 4, 5, 1, 6000)' at line 1 with the insert statement you wrote

BenM Over a year ago

I was in the process of updating the answer. I didn't see you were using SET.

BenM Over a year ago

@MadhurBhaiya Not true: If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows

ADyson Over a year ago

@Simrankaur "my other insert statements work just fine with SET " I doubt it...are you sure they weren't UPDATE statements? Or just possibly it could be as per the example here, which seems to be a mysql-specific extension... stackoverflow.com/questions/861722/… in which case you'd need the x=1,y=2 syntax for the fields, not a bracketed list. You can't mix the two styles as you were doing

BenM Over a year ago

@Simrankaur I'm 100% sure they don't with this syntax. They'll be UPDATE statements: dev.mysql.com/doc/refman/8.0/en/set-statement.html

|

Collectives™ on Stack Overflow

Mysql insert statement syntax error with SET

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related