Compare Two MySQL Tables and Update Foreign Key Column With Any Missing IDs

[mcfc4heatons]

Hello,

What's the correct way to compare two tables and update table 2 with any missing IDs?

Scenario:

Table 1 with primary key 'id'
Table 2 with foreign key column 'related_id' linked to Table 1 'id' column
Compare the two tables and insert any missing IDs into table 2 foreign_key 'related_id' column

Edited April 18, 2021 by mcfc4heatons
typo

[mcfc4heatons]

this returns all the the records from table1 that are not in table2, just not sure how to combine an insert of the missing records into table2 ?

SELECT id FROM table1 WHERE id NOT IN (SELECT related_id FROM table2)

 

Edited April 18, 2021 by mcfc4heatons

[mcfc4heatons]

OK, I worked it out:

    INSERT Into table2 (related_id) SELECT id FROM table1 WHERE id NOT IN (SELECT related_id FROM table2)

 

[Barand]

Best way is to do it manually, noting the steps, and then replicate those steps in code.

Here are my neighbours and their dogs' names. Some have no dog, some have two.

Allocate the dogs to their correct owners

TABLE: neighbour                              TABLE: dog
+-------+----------------+                    +-------+----------------+-----------+
| ID    | name           |                    | ID    | name           |  owner_id |
+-------+----------------+                    +-------+----------------+-----------+
|   1   | Peter          |                    |   1   | Maxwell        |     ?     |
|   2   | Paul           |                    |   2   | Hector         |     ?     |
|   3   | Mary           |                    |   3   | Hugo           |     ?     |
|   4   | Fred           |                    |   4   | Jasper         |     ?     |
|   5   | Wilma          |                    |   5   | Coco           |     ?     |
|   6   | Barny          |                    |   6   | Jaba           |     ?     |
|   7   | Betty          |                    |   7   | Oliver         |     ?     |
+-------+----------------+                    +-------+----------------+-----------+

The only way to do it is by using the "sorting hat" from Hogworts. Or manually.

 

Edited April 18, 2021 by Barand