Ecto - Postgres - update .. set column = (select .. from ...)

How can I write the following query in Ecto?

UPDATE
	"ENTITIES"
SET
	"VERSION" = (
			SELECT
				CASE WHEN("VERSION" = $1) THEN $2 ELSE -1 END
			FROM
				"ENTITIES" 
			WHERE 
                "ENTITY_ID" = $3
	)
WHERE
	"ENTITY_ID" = $3`,

[expectedVersion, newVersion, entityId]

this should work

from(e in "ENTITIES",
  where: e."ENTITY_ID" == ^entityId,
  update: [set: [VERSION: fragment("(CASE WHEN ? = ? THEN ? ELSE -1 END)", e."VERSION", ^expectedVersion, ^newVersion)]]
)
|> Repo.update_all([])
4 Likes

Thanks!