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.Oth开发者_如何学编程erID, 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!
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
You can try:
UPDATE new
SET Data = ( SELECT DATA FROM old WHERE otherID = new.otherID )
WHERE NOT EXIST
( SELECT NULL FROM new AS new2
WHERE new2.id < new.id
AND new2.otherID = new.otherID )
Note that this is standard SQL92 and should work with any RDBMS.
This worked for me in PostgreSQL, though I may have gotten the quoting wrong for MySQL.
UPDATE newtable SET
`Data` = oldtable.`Data`
FROM
oldtable
WHERE
newtable.`ID` IN (
SELECT MIN(sub_newtable.`ID`)
FROM newtable sub_newtable
GROUP BY
sub_newtable.`OtherID`
)
AND newtable.`OtherID` = oldtable.`OtherID`
You can use:
UPDATE `NEW`
LEFT JOIN `OLD`
ON `NEW`.`OtherID` = `OLD`.`ID`
SET `NEW`.`Data` = `OLD`.`Data`
EDIT: I'm sorry, this will update all records that correspond to columns in OLD.
精彩评论