Is it possible to do an update with a joined table...

# exposed

[Zyle Moore]

Is it possible to do an update with a joined table in Expose, either through tables or entities? UpdateStatement doesn't seem to expose a getter. Something like

Creature
    .join(
        RandomizedCreature,
        JoinType.INNER,
        onColumn = Creature.id,
        otherColumn = RandomizedCreature.original_entry
    )
    .update {
        it[Creature.id] = it[RandomizedCreature.randomized_entry]
    }

[Zyle Moore]

The MySQL query I'm trying to replicate is

UPDATE
  creature c
JOIN
  randomized_creature rc
ON
  c.id = rc.original_entry
SET
  c.id = rc.randomized_entry

[Zyle Moore]

I tried to naive replacement, but since it ended up in multiple queries, the data ended up slightly broken

RandomizedCreature
    .select(RandomizedCreature.original_entry, RandomizedCreature.randomized_entry)
    .forEach { rc ->
        val originalEntry = rc[RandomizedCreature.original_entry]
        val randomizedEntry = rc[RandomizedCreature.randomized_entry]
        Creature
            .update({ Creature.id eq originalEntry }) { c ->
                c[Creature.id] = randomizedEntry
            }
    }

This generated lots of updates, but since each one contained a different WHERE, it updated rows that had already been updated.

[Zyle Moore]

The goal is an atomic swap between values. By having multiple updates, for example, all 1s get converted to 2s in the first update, but then all 2s get converted to 1s in the second update, leaving everything with a value of 1, and no 2s.

[Chantal Loncle]

Hi @Zyle Moore Yes it's possible to do both updates and deletes on a Join, using the same DSL as in your first example. It's just that you're using it on both sides of the assignment operator, when you can't (and don't have to).

The following works on MySQL to generate the required SQL syntax you mentioned:

Creature
    .join(
        RandomizedCreature,
        JoinType.INNER,
        onColumn = Creature.id,
        otherColumn = RandomizedCreature.original_entry
    )
    .update {
        it[Creature.id] = RandomizedCreature.randomized_entry
    }