Complex how to update one DB/Table with data from a different DB/Table-CodePudding

I need to populate a new field in an existing table. While at it I want to check and update several of the other fields based on the same source table.

The source is in a table from a different data base with over 1.4 million entries. The target table has about 9,000 entries. My goal is to update the following fields in the target table: 'fccid, city, state, zip' with the current source values. The common field is 'callsign' in both the source and target tables. The issue being the row 'callsign' has multiple entries in the source table and only one in the target. The largest 'fccid' value in source is the correct one to use.

The code below extracts from the source table values I want to update in the target table. But I do not understand how to update the target table with this information for just the records in the target table. Do I need to create a temp file from the output of the source code, and use it for the update code? This would still be a very large table, how do I do the update directly?

Source Code:

SELECT b.callsign, a.fccid, a.city, a.state, a.zip
  FROM fcc_amateur.en  a
 INNER JOIN (
    SELECT callsign, MAX(fccid) fccid
      FROM fcc_amateur.en
      GROUP BY callsign ) b
               ON a.callsign = b.callsign AND a.fccid = b.fccid
;

Target Code:

UPDATE stations SET 
        fccid = b.fccid,
        city = a.city,  state = a. state, zip = a.zip
  WHERE a.callsign = b.callsign
;

CodePudding user response：

You can embed JOIN operations among those 3 tables directly inside the UPDATE statement:

UPDATE     stations s
INNER JOIN (SELECT callsign, MAX(fccid) AS fccid
            FROM fcc_amateur.en 
            GROUP BY callsign) b 
        ON s.callsign = b.callsign
INNER JOIN fcc_amateur.en a 
        ON a.callsign = b.callsign AND a.fccid = b.fccid
SET fccid = b.fccid,
    city  = a.city,  
    state = a.state, 
    zip   = a.zip

In MySQL 8.0, a slightly better performing way of getting your last "fccid" value from your "fcc_amateur.en" table is using the ROW_NUMBER window function, that assigns a ranking value (1, 2, 3, ...) to each "fccid" value in a specified partition (your "callsign"). Once you have that, you can select all first descendently callsign values by filtering them (WHERE ranking = 1).

UPDATE     stations s
INNER JOIN (SELECT callsign, fccid, 
                   ROW_NUMBER() OVER(PARTITION BY callsign ORDER BY fccid DESC) AS rn
            FROM fcc_amateur.en 
            GROUP BY callsign) b 
        ON s.callsign = b.callsign
INNER JOIN fcc_amateur.en a 
        ON a.callsign = b.callsign AND a.fccid = b.fccid
SET fccid = b.fccid,
    city  = a.city,  
    state = a.state, 
    zip   = a.zip
WHERE b.rn = 1