[Proposal] Allow update_all query to contain input rows

[narr...@gmail.com]

Sometimes I want to update lots of rows at once, but with different data for each key or combination of fields.

In PostgreSQL, it works like this:

UPDATE my_table

SET my_table.value = input.value

FROM (VALUES (1, 'abc'), (2, 'def')) AS input(id, value)

WHERE my_table.id = input.id;

and in MySQL, it should work similarly with multiple tables in the list:

UPDATE my_table, (VALUES ROW(1, 'abc'), ROW(2, 'def')) AS input(id, value)

SET my_table.value = input.value

WHERE my_table.id = input.id;

(I haven't tested either but they should be theoretically possible).

Since Ecto.Query can not contain multiple tables in the from clause, which you can work around with lateral joins in select queries, I thought that maybe there could be a new clause especially for updates that's similar to joins in that it can either contain another table, or another new feature, a VALUES expression.

The above queries could then look like this in ecto DSL:

values = Ecto.Query.values([%{id: 1, value: "abc"}, %{id: 2, value: "def"}])

query = from(m in MyTable, update: [set: [value: as(:input).value], from: values, as: :input], where: as(:input).id == m.id)

Repo.update_all(query, [])

Maybe there's a simpler way to write it or it can already be done.

The proposal with the values() function could also be useful in other queries.

[Allen Madsen]

You can do something similar with insert_all, with on_conflict and conflict_target to upsert your updates. Though, there are some semantic differences.

Allen Madsen
http://www.allenmadsen.com

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/ce90e84b-a63b-4103-b3c7-67568d681c8dn%40googlegroups.com.

[José Valim]

We would be glad to consider PRs exploring this. :)

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAK-y3CsdUyyqi9Zyk5CRFEBKa99C0H29LKO7wOAxHpJws9BCDw%40mail.gmail.com.

[narrowtux]

That's true, however, I haven't found a great solution to avoid actually inserting new rows, other than filtering the input data with another query before actually calling insert_all.

[tom.cr...@gmail.com]

Another option, at least in PostgreSQL, would be to use a CTE:

WITH input(id, value) as (values (1, 'abd'), (2, 'def'))

UPDATE my_table t

SET value = input.value

WHERE t.id = input.id;

In Ecto, the with_cte macro can be used to this effect. In any case, I think the values proposal is a good idea