开发者

UPDATE with INNER JOIN or MIN?

开发者 https://www.devze.com 2023-04-03 10:58 出处:网络
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 tak

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消