Update mysql database with array condition

Question

I have an array of unique Ids like the following one:

const myIds = [2, 3, 4];

I also have a database like this one:

+-----+------+
| id  | seen |
+-----+------+
|   0 |    0 |
|   1 |    0 |
|   2 |    0 |
|   3 |    0 |
|   4 |    0 |
+------------+

I would like to update my database so that the seen column of all the ids in my array are set to 1. That would leave me with this:

+-----+------+
| id  | seen |
+-----+------+
|   0 |    0 |
|   1 |    0 |
|   2 |    1 |
|   3 |    1 |
|   4 |    1 |
+------------+

I have tried this code but it only updates one entry:

    db.query('UPDATE table SET seen = 1 WHERE id = ?', myIds,
    function (error, results) {
        if (error) throw error;
        console.log("ok");
    });

Is there any other way to do so ?

Thanks for your help.

cdaiga · Accepted Answer · 2018-03-12 12:31:50Z

1

Try this:

db.query('UPDATE `table` SET seen = 1 WHERE id IN (?)', [myIds.join()],
    function (error, results) {
        if (error) throw error;
        console.log("ok");
    }
);

answered Mar 12, 2018 at 12:31

cdaiga

4,9374 gold badges25 silver badges44 bronze badges

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

1 Comment

nsayer Over a year ago

I am having this error: Error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect DOUBLE value: '2,3,4'

nsayer · Accepted Answer · 2018-03-12 13:35:56Z

1

Thanks @cdaiga I managed to make it work:

db.query('UPDATE table SET seen = 1 WHERE id IN (?)', [myIds],
    function (error, results, fields) {
        if (error) throw error;
        console.log("ok");
});

edited Mar 12, 2018 at 13:35

answered Mar 12, 2018 at 13:08

nsayer

8492 gold badges9 silver badges22 bronze badges

Collectives™ on Stack Overflow

Update mysql database with array condition

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related