0

I have two MySql databases DB1 and DB2. Each of them has tables tbl1 and tbl2. These two tables have the same primary key (Column1). Tables in DB1 have N rows. I want to create new database and table, which will look like:

DB1.tbl1.Col1   | DB1.tbl1.Col2 | ... | DB1.tbl1.ColK | DB1.tbl2.Col2 | DB1.tbl2.ColZ
    ...         |    ...        | ... |     ...       |    ...        |      ...   
DB2.tbl1.Col1+N | DB2.tbl1.Col2 | ... | DB2.tbl1.ColK | DB2.tbl2.Col2 | DB2.tbl2.ColZ
    ...         |    ...        | ... |     ...       |    ...        |      ...   

But, I could not use MERGE as I need indexes in DB2.tbl1.Col1 in new table to be DB3.tbl.Col1=DB1.tbl1.Col1, DB2.tbl1.Col1+N.

I now how to do it on the side of client (on Python), but is it possible to do it via some MySql script on server side?

Thank you.

  • Are you not sure how to create the new table data e.g. with a union all or is it about how to create the new index? – bw_üezi Mar 29 '17 at 8:20
  • First of all I am not sure how to make union. Second, how to create new index. Sorry, I am really not so good in SQL. – zlon Mar 29 '17 at 8:24
  • sorry now I'm confused by your edit – bw_üezi Mar 29 '17 at 9:05
  • I have 2 databases from two sites. tbl1 of each database contains UserID and its email, address, phone and so on. tbl2 contains some information about user actions on the site. Now i want to have 1 table from both sites. I am ready to change UserIDs for second site just by adding number of users on site 1. – zlon Mar 29 '17 at 9:09
1

how about?

create table DB3.tbl1 as
select * from (
  select
    DB1.tbl1.Col1,
    DB1.tbl1.Col2, ...
    DB1.tbl1.ColZ
  from DB1.tbl1
  union all
  select
    DB2.tbl1.Col1 + n
    DB2.tbl1.Col2, ...
    DB2.tbl1.ColZ
  from DB2.tbl1
)

could also replace the + n by + ( select count(*) from DB1.tbl1 )

  • Looks perfect, but I also need to merge tbl1 and tbl2 before... – zlon Mar 29 '17 at 9:00
  • Sorry, it was not in my question table. I've edit it. – zlon Mar 29 '17 at 9:02
  • using union all keeps duplicates where as using just union skip duplicates in tbl2. – bw_üezi Mar 29 '17 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.