How to update multiple records with different values in Ecto.Repo.update_all

I recently ran into a problem where I wanted to update many database records with a single update_all/2.

In this particular case, I was dealing with a table that leveraged parent_id to model a basic parent-child hierarchy.

Through some larger business logic, I end up with a list of tuples [{1, "new root"}, {2, "Child 1"}, {3, "Child 2}] that essentially maps id => new name. The goal is to update all rows and set the new name according to their primary key.

Now, it would be easy to perform a series of individual UPDATE queries through Ecto, but since I'm dealing with a large number of items, it would be more efficient if we could instead perform this update as a single Ecto.Repo.update_all/2 .

PostgreSQL Unnest

PostgreSQL comes with a number of array functions, one of which is unnest. Unnest can be used to expand a list of items into rows.

select * from unnest(ARRAY[1, 2]::integer[]);
 unnest
--------
      1
      2
(2 rows)

It can take multiple arrays of equal size and will create a table.

select * from unnest(ARRAY[1, 2]::integer[], ARRAY['foo', 'bar']::text[]);
 unnest | unnest
--------+--------
      1 | foo
      2 | bar
(2 rows)

We can leverage this to create a join table for our update statement. Since unnest isn't natively supported in Ecto we have to use a query fragment.

fragment(
  "SELECT * FROM unnest(?::integer[], ?::text[]) AS
   id_to_new_name(id, new_name)",
   type(^ids, {:array, :integer}),
   type(^new_names, {:array, :string})
)

To bring it all together, we need to turn the list of tuples into two lists of ids and values

ids, new_names = Enum.unzip([
  {1, "new root"}, 
  {2, "Child 1"}, 
  {3, "Child 2}
])

and run the update:

  Tree
  |> join(
    :inner,
    [t],
    tn in fragment(
      "SELECT * FROM unnest(?::integer[], ?::text[]) 
      AS id_to_new_name(id, new_name)",
      type(^ids, {:array, :integer}),
      type(^new_names, {:array, :string})
    ),
    on: t.id == tn.id
  )
  |> update([_, tn], set: [name: tn.new_name])
  |> DB.Repo.update_all([])

This will perform the update efficiently and with a single call to the database.