Jump to content

Comparing two tables with Inserting and no duplicates


jbradley04

Recommended Posts

Hello,

I need help trying to move users from one DB to another, but avoiding those with duplicates usernames...

This seems to work but is brutally slow! Does anyone have any advice to streamline this process?

 

This is what I have:

SELECT * FROM `db1`.`table1` WHERE  `db1`.`table1``.`username` NOT IN (SELECT  `db2`.`table2`.`username` FROM  `db2`.`table2`) LIMIT 100

 

Thank you so much!

Link to comment
Share on other sites

Sorry, let me be more descriptive.

 

I have two user tables, both with the same fields but in different DB's.

Basically, I am looking to merge the two user tables into one DB and one table, lets call `user`

Now, when the conflicting area would be the usernames. So I am looking to move only usernames that are unique to the second DB. So if DB1.user had username of 'test' and DB2.user had username 'test' then this will not get moved over.

 

The biggest issue though is that there are tables in both DB's that depend on the userID of these usernames to correlate them to their respective privileges. So, if I change the data to the new table, its userID changes to a new userID because they are Auto Incremented. So I am looking to make the changes, then get the new insert ID and change the tables that are dependent on the userID.

 

I hope I am explaining this enough. I appreciate all the responses.

Link to comment
Share on other sites

I'm not. But it is still giving me zero rows... this is what I have...



$sql = mysql_query("SELECT *
FROM    `tb1`.`users`");


while ($row = mysql_fetch_array($sql))
{
$sql2 = mysql_query("INSERT IGNORE INTO `tb2`.`users` 
(`user_type`,`group_id`, `user_permissions`,  `username`) 
VALUES
('".$row['user_type']."','".$row['group_id']."','".$row['user_permissions']."','".$row['username']."')");
$latestID = mysql_insert_id();
echo $latestID."<br>";

Link to comment
Share on other sites

You should be able to do this all in SQL, no need to loop and do all those queries. 

 

You said the two tables had the same structure? Is it possible for there to be a row in table 1 that has the same username as table 2, and different information (besides the id)?

Link to comment
Share on other sites

Only part I care about is username. So, if the username is the same I dont want it. If the username is the different, I am transferring it. Long story, but I ran a script that Identified who was similar based on other info. So ones that were same name but different info were appended to with random characters so they would be unique.

 

So, I guess what I need to find out is how to run this based solely on usernames and nothing else? When there is not a duplicate username it will be added, otherwise ignored.

Thanks for all your help!

Link to comment
Share on other sites

Possible solution.

 

Add extra column "oldID" to the table you are inserting to and put the orig id in there on insertion.

 

You can then use this to update the other tables from the old id to the new id.

 

edit

 

EG

UPDATE childTable ct
JOIN users u ON ct.userid = u.origID
SET ct.userid = u.id

Edited by Barand
Link to comment
Share on other sites

There's also another way you might be able to do it, if you're using proper foreign key relations which cascades on updates.

 

Just transfer the unique usernames from the second table, giving them their new user IDs. Do make sure that the first newest user ID, in the new table, is higher than the ID for the last non-unique user in the old table. If it isn't then this method might not work.

Anyway, once that is done you can update the old table with the new user IDs, taking care to start with the highest new ID first. At which point the foreign key relations will do the rest of the job for you, and ensure that all records are updated to reflect the new user ID. All you have to do afterwards, is to update the relations to point to the correct table, before deleting the old table.

 

You might also want to delete the non-unique usernames from the second table, or at the very least ensure that the new and old user IDs match. Otherwise you're going to have a lot of mismatched data once up update the relations.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.