How to implement this dynamic update in Ecto?

Questions / Help
1 
UPDATE mytable
SET 
  text = myvalues.mytext,
  version = myvalues.myint
FROM (
  VALUES
    (1, 'textA', 99),
    (2, 'textB', 88),
    ...
) AS myvalues (mykey, mytext, myint)
WHERE mytable.id = myvalues.mykey

I’ve checked the Programming Ecto book, some articles, the docs, but I didn’t find an element and high-performance way to do this.

2

I’m using the loop way: Ecto Multi in a Loop

Is there a more effective way?

3

Using upserts might work for you

updates = [
    %{id: 1, text: "textA", version: 99},
    %{id: 2, text: "textB", version: 88},
]

{:ok, updated} = MyRepo.insert_all(Post, updates, on_conflict: {:replace, [:text, :version]}, conflict_target: [:id])

This will either insert or update rows based on the id in each of your updates.

3 Likes
4

if the columns of my table is not just text and version, and the input id not exist, I think there is problem when use up code.

5

That’s correct, you’d only be able to use this code to perform updates (which is what you were asking for). You’d have to handle the initial inserts separately.

If you wanted to handle both the initial insert and subsequent updates with a single statement you’d need to provide all of the values to updates = [...] (the var in the example). In that case all of the values would be used for the initial insert but updates would only replace the text and version.

6

No, I don’t want to handle both, just the updates.

So, If there is a way to ignore the “not exist” id error when using insert_all upserts, that is what I want.