xoops forums
  1. Board index
  2. General XOOPS.org forums
  3. XOOPS Project Teams
  4. XOOPS Support Team
  5. how to fusion two database ?
Register

cybermongo

Just popping in
Posted on: 2016/2/7 15:08
cybermongo
cybermongo (Show more)
Just popping in
Posts: 27
Since: 2004/10/4
#1

how to fusion two database ?

2016/2/7 15:08 hi every one, i need your help to join two Database of my two xoops website.
the module News create a post with a different ID that make an error when i try to paste de data just in one BD, because this look duplicate, please need your help, how to join these two database in just in one. all the the data are coming from Module news.
Cheers guys

Mamba

Moderator
Posted on: 2016/2/7 18:29
Mamba
Mamba (Show more)
Moderator
Posts: 10818
Since: 2004/4/23
#2

Re: how to fusion two database ?

2016/2/7 18:29 Depending on how many records are duplicate. If the number is small, you could use the "INSERT IGNORE", and then identify the duplicate records that were not inserted, changed manually their ID and then insert them.

Or you can follow this advice from StackOverflow:

Quote:
First, merge in a temporary table, with something like:

create table MERGED as select * from table 1 uni-on select * from table 2

Then, identify the primary key constraints with something like

SELECT COUNT(*), PK from MERGED GROUP BY PK HAVING COUNT(*) > 1

Where PK is the primary key field...

Solve the duplicates.

Rename the table.
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

cybermongo

Just popping in
Posted on: 2016/2/7 18:58
cybermongo
cybermongo (Show more)
Just popping in
Posts: 27
Since: 2004/10/4
#3

Re: how to fusion two database ?

2016/2/7 18:58 thnk you Mamba, there are too many record and actually these are not duplicated , the duplicate field is just the "storyid" in this incremente automaticly i will try the script below

SET @db:='id_new';   select @max_id:=max(AUTO_INCREMENT) from information_schema.tables;  select concat('update ',table_name,' set ', column_name,' = ',column_name,'+',@max_id,' ; ') from information_schema.columns where table_schema=@db and column_name like '%id' into outfile 'update_ids.sql';  use id_new source update_ids.sql;
i will let you know any news

goffy

Quite a regular
Posted on: 2016/2/11 13:25
goffy
goffy (Show more)
Quite a regular
Posts: 384
Since: 2010/12/27
#4

Re: how to fusion two database ?

2016/2/11 13:25 hi cybermongo

the problem, maybe, will be, that the storyid you find also in the other tables like stories_files.
I would recommend:
1) add a new column "storyid_new" to your table stories
2) Update this column by new storyid, e.g. if the max storyid from the other database is 2000 then run sql
UPDATE stories SET storyid_new = storyid + 2000;

3) Update the values from the other tables with storyid_new
UPDATE stories_files a JOIN stories b 
   ON a.storyid = b.storyid SET a.storyid = b.storyid_new

4) do the same with the other tables which are using storyid