I ended up just doing a quick script to find duplicates, and then deleting duplicates. Then I exported to csv with "select into outfile" every field but the "id" then imported from csv every field but the "id" so that the imported data would get new id, then I used the new id's on the secondary table which ended up not having as many as I initially thought there would be. Thanks all for the input.
On 19 May 2010, at 18:28, William Attwood wrote: > Ahhh, a section I didn't include - what if the values are already in the new > database's secondary table with different ID #'s, you'd have to check by the > VALUE in each array and find out which ID's are different and assign those > accordingly - that's a few steps which should be done prior to creating the > new array of values not in the new database. > > > On Wed, May 19, 2010 at 6:26 PM, William Attwood <[email protected]> wrote: > Jonathan-- > > Let's outline the scenario, you have two databases, four total tables > (concept applies for more): > > • Database1 > • Table1-1 > • Table2-1 > • Database2 > • Table1-2 > • Table2-2 > Your process is as follows (PHP Script): > > • SELECT * FROM Database1.Table2-1 into ARRAY1 (id=>val) > • SELECT * FROM Database2.Table2-2 into ARRAY2(id=>val) > • INSERT INTO ARRAY3 (ALL FROM ARRAY2 NOT IN ARRAY1) > • FOR EACH ELEMENT PUT INTO ARRAY3, ASSIGN ARRAY1[id][newid] = > newid > • SELECT * FROM Database1.Table1-1 into ARRAY4 > • FOR EACH ELEMENT IN ARRAY4 (data to merge into Database2) GET > ARRAY1.referenceid (Table2-1) > • SET ARRAY4.referenceid = ARRAY2[id][newid] > (Table2-1->Table2-2) > • INSERT INTO Database2.Table2-2 ARRAY3 ELEMENTS > • INSERT INTO Database2.Table1-2 ARRAY1 ELEMENTS > > This way you get all new values and new ID's for them in Step3, you keep the > old ID and new ID in Step3.1, you then get all data from your Database1 table > with those referential links, you update those according to the newid, and > then insert all new data into your second database. > > Did I miss anything? I'm in a hurry, I hope that's easy to understand. I > could have named the Arrays better. > > -Will > > On Wed, May 19, 2010 at 5:53 PM, Jonathan Duncan > <[email protected]> wrote: > UPHPU, > > Here is a database conundrum. I have two databases with identical tables > (same structure different data). They have been on different sites though > collecting data from different groups of people. I now need to merge that > data. There is one table that relies on the 'id' key of another table. So > the id's well need to be changed, and the other table updated to match. Any > ideas? > > I think I like this way the best: > http://www.everymanhosting.com/forum/about22.html > > However, this will not address the required cascading update. > > This has some good information: > http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/ > > But also not a final solution. > > I am thinking I may just write a PHP script that will select from db1.table1, > insert it into db2.table1 and then check if there is an associated row in > db1.table2 and if so insert that in db2.table 2 with new 'id' key from last > insert on db2.table1. > > Thoughts? > > Thanks, > Jonathan (aka SunSparc) > > > > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net > > > > -- > Take care, > William Attwood > Idea Extraordinaire > [email protected] > > > > -- > Take care, > William Attwood > Idea Extraordinaire > [email protected] _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
