Error: ER_PARSE_ERROR: You have an error in your SQL syntax; for multiple insertion

Question

I am doing an application with Node js, Express and MySQL. I am running on localhost with XAMPP, not sure if this information is important. I want to insert multiple rows in my database, so I did:

    db.query('INSERT INTO tasks (done, user_id, task) VALUES ?', values, (err, result) => {
            if(err) {
                console.log(err);
            } 
            console.log("done");

        });

where values is an array of arrays containing each a triplet (done, user_id, task), example [[0, 10, 'foo'], [1, 10, 'bar']]. However, when this snippet runs with more than one array in values it gives me an error like

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0, 10, 'test'' at line 1

What am I doing wrong? Is there another way to insert multiple rows?

** EDIT: ** My problem is multiple row insertion, I know that the generated query has the wrong syntax, but according to this accepted answer my Javascript syntax sould be correct

andrejb-dev · Accepted Answer · 2020-12-10 10:31:36Z

1

As it is stated in the mentioned answer, your values should be wrapped in another brackets [values]

Note: values is an array of arrays wrapped in an array

edited Dec 10, 2020 at 10:31

answered Dec 9, 2020 at 9:11

andrejb-dev

2,4771 gold badge36 silver badges49 bronze badges

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

3 Comments

Essay97 Over a year ago

The problem is with multiple insertion, my javascript syntax was taken by this accepted answer, maybe I have to edit and clarify the question

andrejb-dev Over a year ago

I see, in that example they have array of array too, but they wrap it in another one by [values] as the query parameter so it becomes "an array of arrays wrapped in an array" as stated in that answer. Did you tried it that way?

Essay97 Over a year ago

Oh, I didn't notice it, now it works! Edit the answer with this information, I'll mark as accepted

Đinh Quốc Việt · Accepted Answer · 2020-12-09 09:02:01Z

0

I don't know variable "values" look like, but you need check syntax.

right syntax is : INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

answered Dec 9, 2020 at 9:02

Đinh Quốc Việt

499 bronze badges

2 Comments

Essay97 Over a year ago

values is for example [[0, 10, 'foo'], [1, 10, 'bar']]. I can't replace values one by one because I don't know how many do I have to insert every time. According to this accepted answer my Javascript syntax is correct for multiple row insertion, but it actually messes up SQL syntax

Đinh Quốc Việt Over a year ago

you need convert [[0, 10, 'foo'], [1, 10, 'bar']] to (0, 10, 'foo'), (1, 10, 'bar')

Collectives™ on Stack Overflow

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; for multiple insertion

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related