So what would be the Ecto way to do this:
- Need to update potentially a lot of rows.
- The conditionals is a list, all the same fields to match but different values for every one.
- Also a static value to update on them all (think
updated_at).
In PostgreSQL you’d generally do something like this:
UPDATE blah AS b SET
field1 = v.field1,
field5 = "newvalue"
FROM (VALUES
("something", "bloop", 42, 6.28),
("another", "bleep", 6, 1.1),
...morevalue
) as v(field1, field2, field3, field4)
WHERE
b.field2 = v.field2 AND
b.field3 = v.field3 AND
b.field4 = v.field4
Or in a transaction create a temporary table, fill it, and join across them or so.
None of these I can seem to figure out how to do in Ecto-speak due to query design limitations, so I’d have to drop down to raw SQL (yay not catching typing bugs…), so that’s what I’m doing for now (via temp table).
Anyone have ideas on how would this pattern be done in normal Ecto without dropping to SQL? Given a static value to update in addition to a list of values that both include things to match on as well as values to update on a per-row basis.
I guess I could do a dynamic build chain with an utterly ginormously massive WHERE clause, but I don’t think that would be terribly efficient and would break upon a certain size…
to understand the SQL statement above.
