Jump to content

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


mcfc4heatons
 Share

Recommended Posts

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 by mcfc4heatons
typo
Link to comment
Share on other sites

Posted (edited)

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 by mcfc4heatons
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.