使用 INNER JOIN 或 MIN 更新? [英] UPDATE with INNER JOIN or MIN?

问题描述

我正在尝试在表之间传输一些数据.'NEW' 表可以有多个数据条目，而这些数据最初并不意味着在 'OLD' 表中有多个条目.我想从旧"表中获取数据并将其复制到新表中，其中 NEW.ID 最低，其中 new.OtherID=old.OtherID，基本上每组其他 ID 的 MIN(ID) 等于彼此.

I am trying to transfer some data between tables. The 'NEW' table can have multiple entries of the data that was originally not meant to have multiple entries in the 'OLD' table. I would like to take the data from the 'OLD' table and copy it over to the new table where the NEW.ID is the lowest where new.OtherID=old.OtherID, basically a MIN(ID) per group of OtherID's equal to each other.

新"表

ID | OtherID | Data
1       1      NULL
2       1      NULL
3       2      NULL
4       3      NULL
5       3      NULL

'旧'

OtherID | Data <br>
1            data1
2            data2
3            data3
4            data4
5            data5

更新后的新"表格的预期结果:

Desired Outcome on updated 'NEW' table:

ID | OtherID | Data <br>
1       1       data1
2       1       NULL
3       2       data2
4       3       data3
5       3       NULL

等

谢谢！

推荐答案

这是如何使用 INNER JOIN 和 更新 在 MySQL 中:

This is how you could use INNER JOIN with UPDATE in MySQL:

UPDATE NEW n
  INNER JOIN (
    SELECT
      OtherID,
      MIN(ID) AS ID
    FROM NEW
    GROUP BY OtherID
  ) m ON n.ID = m.ID
  INNER JOIN OLD o ON n.OtherID = o.OtherID
SET n.Data = o.Data

这篇关于使用 INNER JOIN 或 MIN 更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！