Comparing two tables with Inserting and no duplicates

[jbradley04]

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!

[jbradley04]

Just one other thing. I know this is not the insert, this is just my query to find all non Duplicate usernames.

I was going to do a search then throw the insert in a while loop.

[Christian F.]

Much better to do this in one query with INSERT INTO ... SELECT .....

 

Also, please post the results of EXPLAIN {your select query}.

Edited January 12, 2013 by Christian F.

[Barand]

Before you do that, add a UNIQUE KEY constraint to the username (if there isn't one and it isn't primary key) then

 

INSERT IGNORE INTO ... SELECT ...

[jbradley04]

I thought about that, but my question is, I have to update other fields using the insert ID from these inputs. How can I go about that? Also, how would I do a loop to run each one, and get ID's, then make other changes to Tables?

Thanks

[Barand]

Perhaps you should tell us the full story before we waste any more time on partial solutions based on guesswork

[jbradley04]

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.

[Jessica]

Make the username field unique and use ON DUPLICATE IGNORE

[jbradley04]

Thanks, Jessica.

Problem is because userID and username are both unique it seems to show zero rows. I dont care about userID because they are auto incrementing and will be assigned a new one. Is there any way to do IGNORE on just username field?

Thanks!

[Jessica]

Why are you trying to insert the userID anyway?

[jbradley04]

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>";

[Jessica]

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)?

[jbradley04]

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!

[Jessica]

I'm sure you can do it in one query, and Barand is most likely to have the best answer I'll be back in a bit.

[Barand]

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 January 13, 2013 by Barand

[Christian F.]

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.

[jbradley04]

Thanks everyone, I will get working on this today!

I really appreciate all the input!

J