How to update entities in a table from an array?

Question

I'm trying to figure out how to update many elements at once. Suppose I have the following array:

[
     {
          id: 100,
          order: 1,
     },
     {
          id: 101,
          order: 2,
     },
     {
          id: 102,
          order: 3,
     },
]

I then transform this array, replacing the values of order. The resulting array becomes the following:

[
     {
          id: 102,
          order: 1,
     },
     {
          id: 101,
          order: 2,
     },
     {
          id: 100,
          order: 3,
     },
]

I use this on the frontend to render a list in the appropriate order, based on the value of order.

But how can I update these 3 entities in my database?

I can obviously make 3 UPDATE statements:

const promises = [];

newArray.forEach(({ id, order }) => {
     promises.push(
          // executeMutation is just a custom query builder
          executeMutation({
               query: `UPDATE my_table SET order = ${order} WHERE id = ${id}'`
          })
     )
})

await Promise.all(promises)

But is it possible to do this in one query?

This might help you - https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql — Hendrik, Nov 22 '21 at 15:30
Or maybe if you are using postgresql 9.5+ and `id` is a distinct value you could use `INSERT` instead. `INSERT INTO my_table (id, order) VALUES (102, 1), (101, 2), (100, 3) ON CONFLICT (id) DO UPDATE SET order = excluded.order;` See - https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql — Hendrik, Nov 22 '21 at 15:41
Is this like an upsert? This may be useful, I'll look into it, but I'm not sure this answers my question, these records will always exist in the table at this point — Mike K, Nov 22 '21 at 15:49

score 0 · Answer 1 · answered Nov 25 '21 at 00:05

You can do this using the UNNEST function. First you'll need to handle the query parameters properly. https://www.atdatabases.org/ does this for you, otherwise you need to separately pass a string with placeholders and then the values. If you use @databases, the code could look like:

await database.query(sql`
  UPDATE my_table
  SET order = updates_table.order
  FROM (
    SELECT
      UNNEST(${newArray.map(v => v.id)}::INT[]) as id,
      UNNEST(${newArray.map(v => v.order)}::INT[]) as order
  ) AS updates_table
  WHERE my_table.id = updates_table.id
`);

The trick here is that UNNEST lets you take an array for each column and turn that into a kind of temporary table. You can then use that table to filter & update the records.

How to update entities in a table from an array?

1 Answers1