Ecto Update list of matching values

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…

3 Likes

I wish you had provided some sample data / tables to understand what you want to do. Not that I will have an answer, but out of curiosity :slight_smile: to understand the SQL statement above.

In essence, given this table:

field0 field1 field2 field3 updated_at
0 "hello" "test" 42 1990-01-01
1 "world" "tester" 13 1990-02-01
2 "goodbye" "testing" 18 1990-03-01
etc...

And want to do something like WHERE field1 = "hello" AND field2 = "test" then set field0 to something like 18 and update the updated_at, except repeat this for a lot of rows.

1 Like