Ecto support for Postgresql UPDATE… FROM… AS syntax

Questions / Help
1

Hello! Anyone knows how to build such query using Ecto? I’m afraid neither of update_all or Ecto.Query.update syntax supports it:

UPDATE
    registrations
SET
    name = values.column_name
FROM (
    VALUES
        (1, 'test one'),
        (2, 'test two'),
        (3, 'test three'),
        (4, 'test four')
) AS values (column_id,  column_name)
WHERE
    values.column_id = registrations.id

Source: Bulk Insert and Update in PostgreSQL | by akhouri | Medium

Thanks!

2

You can access the UPDATE...FROM... syntax by using a join in update_all

from(p in Posts, 
  join: c in Comments, on: c.post_id == p.id, 
  update: [set: [post_column: c.some_other_column]]
)
|> Repo.update_all([])

But Ecto doesn’t support value lists right now. So your exact example can’t be done.

For some background on why it is a join, this is essentially what Postgres does:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn’t join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

3

This shows an alternative to value lists using unnest: Unnest for runtime sorted results | Benjamin Milde

json_to_recordset is also great, but I don’t have a good example ready.

3 Likes
4

Thanks for the help, I ended up using the join + unnest approach :slight_smile:

I could be cleaner with some macros but for now it works for my use cases, see the code here: accent/operations_update_all_dynamic.ex at master · mirego/accent · GitHub