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

Question

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
;

You should read about the [UPDATE](https://dev.mysql.com/doc/refman/8.0/en/update.html) statement, and look at this Q/A [MySQL, update multiple tables with one query](https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query) — Luuk, Dec 14 '22 at 19:37

lemon · Accepted Answer · 2022-12-14T22:35:24.303

0

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

edited Dec 14 '22 at 22:35

answered Dec 14 '22 at 19:49

lemon

14,875
6
18
38

I'm not sure what is happening. Your "slightly better" solution fails, I think because MySQL is an older version. So tried your first idea, it ran for several hours before I stopped it. Then I asked it to run for just one value from the stations table and it failed with "#1205 - Lock wait timeout exceeded; try restarting transaction" I tried a restart and got the same thing. Any thoughts? – Keith D Kaiser Dec 14 '22 at 22:30
The first execution of the first solution was never stopped, that's the reason why you're getting that error. Follow here to unlock tables by the current processes >> https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im – lemon Dec 14 '22 at 22:35
If you want to speed up the process on the full amount of records, you need to add indices on the fields on which you apply the join operations. Can you update your post with some sample data and schema with datatypes? – lemon Dec 14 '22 at 22:37
1

#lemon After killing an older process and adding 's.' to the set statement the first suggested method worked. It took about 15 minutes to run but very much worth the wait. Thanks.. – Keith D Kaiser Dec 15 '22 at 20:49

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

1 Answers1