5 replies

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!

Drizzle Team•3y ago•

5 replies

Mendy

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!

How to update multiple rows with one query?

Similar Threads

How to update multiple rows with one query?

Similar Threads

Similar Threads

Similar Threads