3

I need to swap values that have unique constraints. I'm trying to use the update_all function as shown below.

from(e in Episode, where: e.show_id == ^id, update: [set: [position: fragment("position + 1")]])
|> Repo.update_all([])

When using this I get an error because of a duplicate position:

ERROR (unique_violation): duplicate key value violates unique constraint "position_show_id_index"
table: episodes
constraint: position_show_id_index
Key ("position", show_id)=(2, 27) already exists.

How can I swap these position values simultaneously?

Joe Rocca
  • 178
  • 1
  • 10
  • 1
    I think one way to go would be to just use a transaction and first check and maybe delete the previous record and then just insert a new one. – NoDisplayName Feb 17 '17 at 20:03

1 Answers1

1

In general, there is no "nice" way to swap unique indexed values in SQL.

Two known solutions are 1) deleting and reinserting the rows, or 2) updating the rows to another placholder value before updating both of them.

You could take a similar approach in Ecto like this:

Solution 1:

episode = Repo.get(Episode, id)
next_episode = Repo.get_by(Episode, position: episode.position + 1)

if next_episode, do: Repo.delete(next_episode)

episode
|> Episode.changeset(%{position: episode.position + 1})
|> Repo.update()

if next_episode do
  Repo.insert(%Episode{next_episode | position: episode.position})
end

Solution 2: (You'll need to choose some "impossible" value as a placeholder. For example, if position must be positive, you can use a negative number)

episode = Repo.get(Episode, id)
next_episode = Repo.get_by(Episode, position: episode.position + 1)

if next_episode do
  next_episode
  |> Episode.changeset(%{position: -1})
  |> Repo.update()
end

episode
|> Episode.changeset(%{position: episode.position + 1})
|> Repo.update()

if next_episode do
  next_episode
  |> Episode.changeset(%{position: episode.position})
  |> Repo.update()
end

The second solution might be preferable if you have any foreign key constraints referencing Episode.

Tom Schuster
  • 13
  • 5