How to update multiple rows in a table?

Question

SQL

The task is to delete (update) several rows in a table.
Request :

db.query("UPDATE products SET deleted = true WHERE id IN ${id};", products);

where, products

var products = { 
id: [1,5,7]

The array is formed on the client, it is not known in advance what values ​​there are.
Now I'm getting "syntax error at or near array" error. I don't understand the best way to organize the request. Help me please.
PS PostgreSQL 9.5

Answer 1

It would not hurt to clarify what language you are using, but oh well.
In general, it's better to generate a request with parameters and then substitute them.
If the number of parameters is unknown, then you can add them in a loop. You will get something like the following (the result will depend on your language).

var query = "UPDATE products SET deleted = true WHERE id  IN(${p1}";
for(var i = 2; i <= products.length; i++)
{
    query += ", ${p" + i + "}"; 
}
query += ");";
return db.query(query, products);

For your example, the request should look like this:

UPDATE products SET deleted = true WHERE id  IN(${p1}, ${p2}, ${p3});

Answer 2

changed the products variable to an array var products = [5,7,9];
Changed the request - it worked, but I don't like it, because Substituting data directly into the request is a great evil. pg-promise has a defense for this, but frankly, their documentation is a bit hard to figure out.

return db.query("UPDATE products SET deleted = true WHERE id  IN(" + products + ");");