XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. General XOOPS.org forums / 
  3. XOOPS Project Teams
  4. XOOPS Support Team 
  5.  / how to fusion two database ?
Register
1
cybermongo
how to fusion two database ?

  • 2016/2/7 15:08

  • cybermongo

  • Just popping in

  • Posts: 27

  • Since: 2004/10/4


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
2
Mamba
Re: how to fusion two database ?

  • 2016/2/7 18:29

  • Mamba

  • Moderator

  • Posts: 11455

  • Since: 2004/4/23


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.11 | Docs | Modules | Bugs
3
cybermongo
Re: how to fusion two database ?

  • 2016/2/7 18:58

  • cybermongo

  • Just popping in

  • Posts: 27

  • Since: 2004/10/4


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
4
goffy
Re: how to fusion two database ?

  • 2016/2/11 13:25

  • goffy

  • Just can't stay away

  • Posts: 547

  • Since: 2010/12/27


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

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

152 user(s) are online (114 user(s) are browsing Support Forums)

Members: 0

Guests: 152

more...

Donat-O-Meter

Stats
Goal: AU$15.00
Due Date: Jul 31
Gross Amount: AU$0.00
Net Balance: AU$0.00
Left to go: AU$15.00
Make donations with PayPal!

Latest GitHub Commits