How to update multiple rows with one query?

Take this array as an example:

const data = [
  {id: 1, col1: "col1 data where id = 1"}
  {id: 2, col1: "col1 data where id = 2"}
  …(potentially tens of rows)
]

const data = [
  {id: 1, col1: "col1 data where id = 1"}
  {id: 2, col1: "col1 data where id = 2"}
  …(potentially tens of rows)
]

Is it possible to update all the rows with the new data in one query? Using plain sql, something like this will be possible:

WITH data(id, col1) AS (
  VALUES
    (1, 'col1 data where id = 1'),
    (2, 'col1 data where id = 2'),
)
UPDATE my_table
SET col1 = data.col1
FROM data
WHERE my_table.id = data.id;

WITH data(id, col1) AS (
  VALUES
    (1, 'col1 data where id = 1'),
    (2, 'col1 data where id = 2'),
)
UPDATE my_table
SET col1 = data.col1
FROM data
WHERE my_table.id = data.id;

Thanks for your help!

3 Replies

MendyOP•2y ago

Btw, this is something which even Prisma doesn’t support yet, but I’m wondering since drizzle is very different in its approach, maybe there’s a better way of doing this (other then writing a raw sql query)..

Lautaro_dapin•2y ago

most orms use CASE WHEN for updating you could investigate that

MendyOP•2y ago

Thank you, I will check that out! Although, it’s a bit slow - having to compare each row against all cases. But it’s probably better than making multiple single updates. Btw, here’s a third approach I stumbled upon, which although it works it made me smile:

INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c);

INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c);

Gaming

Programming

How to update multiple rows with one query?