mysql batch select_How to batch update (from a select) with MySQL

I inherited from an old database with very similar data split per company department. My goal is to merge everything in one schema. As each department schema has its own unique id, I need to update the foreign keys with the new id after the merge.

To illustrate my issue with an example, let's consider these two tables:

foobar

id name foreign_id

-- ---- ----------

1 foo 4

2 bar 6

3 baz 8

4 qux 4

translate

old_id new_id

------ ------

7 3

9 4

8 9

4 1

6 5

The translate table represent the migration information from the old id (per department) to the new id (in the merged schema).

I would like to update all foreign keys to replace the old id with the new one.

A fully manual solution would be to do this:

UPDATE foobar SET foreign_id = 3 WHERE foreign_id = 7

...

But this solution will eventually generate collisions, so I could use a temporary column instead:

UPDATE foobar SET new_foreign_id = 3 WHERE foreign_id = 7

...

Then drop the foreign_id column and rename new_foreign_id.

I am looking for a more straightforward solution where I could do something like:

UPDATE foobar SET foreign_id = new_id

FOREACH --

SELECT * FROM foobar LEFT JOIN translate ON old_id = foreign_id

Is this possible?