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
    Commented Mar 29, 2017 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
    Commented Mar 29, 2017 at 8:24
  • sorry now I'm confused by your edit
    – bw_üezi
    Commented Mar 29, 2017 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
    Commented Mar 29, 2017 at 9:09

1 Answer 1


how about?

create table DB3.tbl1 as
select * from (
    DB1.tbl1.Col2, ...
  from DB1.tbl1
  union all
    DB2.tbl1.Col1 + n
    DB2.tbl1.Col2, ...
  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
    Commented Mar 29, 2017 at 9:00
  • Sorry, it was not in my question table. I've edit it.
    – zlon
    Commented Mar 29, 2017 at 9:02
  • using union all keeps duplicates where as using just union skip duplicates in tbl2.
    – bw_üezi
    Commented Mar 29, 2017 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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