UPDATE with INNER JOIN or MIN?

Question

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.

'NEW' table

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

'OLD'

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

etc

Thanks!

Answer 1

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