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 un...

问题:

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 -- <-- Magic keyword
    SELECT * FROM foobar LEFT JOIN translate ON old_id = foreign_id 

Is this possible?


回答1:

You can use UPDATE + JOIN. For example:

UPDATE foobar 
RIGHT JOIN translate ON translate.old_id = foobar.foreign_id 
SET foobar.foreign_id = translate.new_id 
  • 发表于 2019-01-21 14:04
  • 阅读 ( 192 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除