How can I do update multiple rows in mysql?

Question

I have a table coach. Table coach have data like this :

I want to update player_id column so the data looks like this:

Player_id value with type = "coach" is taken from the value of the id that has type = "player". It was taken based on the relationship between the value player_code in the field information with field code

I tried using a query self join to update like this :

UPDATE coach
SET player_id = (
            SELECT b.id
            FROM coach a
            LEFT JOIN coach b ON REPLACE(JSON_EXTRACT(b.information, "$.player_code"), '"', '') = b.code
            WHERE b.`type` = 'player'
          )
WHERE `type` = 'coach'

When the query was executed, there exist error like this :

Error Code: 1093
You can't specify target table 'coach' for update in FROM clause

is there anyone can help me?

Answer 1

update coach as c1
inner join (
    select b.id,
        b.code
    from coach b
    where b.`type` = 'player'
    ) as c2 on REPLACE(JSON_EXTRACT(c1.information, '$.player_code'), '"', '') = c2.code

set c1.player_id = c2.id
where c1.type = 'coach';