Ecto: How to update all records with a different random number

user_4579533 0
Viewed 1645

I have a table in postgresql and i want to update the value of column "points" for all the records with a random number. In other languages we could loop over all the db records but how can i do it with ecto? I tried this:

 Repo.all(from u in User, update: User.changeset(u, %{points: :rand.uniform(100)}))

but it outputs the following error:

== Compilation error in file lib/hello_remote/user.ex ==
** (Ecto.Query.CompileError) malformed update `User.changeset(u, %{points: :rand.uniform(100)})` in query expression, expected a keyword list with set/push/pop as keys with field-value pairs as values
expanding macro: Ecto.Query.update/3
lib/hello_remote/user.ex:30: HelloRemote.User.update_points/0
expanding macro: Ecto.Query.from/2
lib/hello_remote/user.ex:30: HelloRemote.User.update_points/0
(elixir 1.10.4) lib/kernel/parallel_compiler.ex:304: anonymous fn/4 in Kernel.ParallelCompiler.spawn_workers/7

I've also tried this:

from(u in User)
|> Repo.update_all(set: [points: Enum.random(0..100)])

but it updates all the records with the same value

phoenix ecto elixir postgresql
2 Answers

You can use fragment/1 with update_all/3, calling a PostgreSQL function to calculate the random values, for example:

update(User, set: [points: fragment("floor(random()*100)")])
|> Repo.update_all([])
user_9064910

I don't think it is possible with the update_all function. The function was created to update many rows with the same value.

You can create a loop and update each record separately, it's not nice but a working solution.

User
|> Repo.all()
|> Enum.each(fn user -> update_user(user) end)

def update_user(user) do
  user 
  |> Ecto.Changeset.cast(%{"points" => Enum.random(0..100)}, [:points])
  |> Repo.update!()
end

If you would like to do it one call you can construct a raw SQL query and use that.

user_4755650
Related