How can I merge two MySQL tables?

Question

How can I merge two MySQL tables that have the same structure?

The primary keys of the two tables will clash, so I have take that into account.

When you say that the PKs might clash, do you mean that there might be duplicate rows and you don't want them copied, or that you need to assign a new PK to one of them because they're really different rows despite having the same PK? (yet another reason to use natural primary keys) — Tom H, Apr 07 '09 at 13:08

score 129 · Accepted Answer · answered Apr 07 '09 at 13:04

129

You can also try:

INSERT IGNORE
  INTO table_1 
SELECT *
  FROM table_2
     ;

which allows those rows in table_1 to supersede those in table_2 that have a matching primary key, while still inserting rows with new primary keys.

Alternatively,

REPLACE
   INTO table_1
 SELECT *
   FROM table_2
      ;

will update those rows already in table_1 with the corresponding row from table_2, while inserting rows with new primary keys.

answered Apr 07 '09 at 13:04

fcw

2,274
1
14
7

2

Actually, it will [REPLACE](http://code.openark.org/blog/mysql/replace-into-think-twice) existing rows (delete + insert), not update. – Cees Timmerman Aug 27 '15 at 09:06
what about if there is any foreign key used for table 2????? How you will updated that? – Kshitiz Sep 05 '18 at 09:02

score 40 · Answer 2 · edited May 23 '17 at 10:31

It depends on the semantic of the primary key. If it's just autoincrement, then use something like:

insert into table1 (all columns except pk)
select all_columns_except_pk 
from table2;

If PK means something, you need to find a way to determine which record should have priority. You could create a select query to find duplicates first (see answer by cpitis). Then eliminate the ones you don't want to keep and use the above insert to add records that remain.

Quassnoi · Answer 3 · 2012-06-05T13:32:14.157

22

INSERT
INTO    first_table f
SELECT  *
FROM    second_table s
ON DUPLICATE KEY
UPDATE
        s.column1 = DO_WHAT_EVER_MUST_BE_DONE_ON_KEY_CLASH(f.column1)

edited Jun 05 '12 at 13:32

answered Apr 07 '09 at 13:00

Quassnoi

413,100
91
616
614

1

Thanks for the good idea. The above cmd gives me a syntax error though. But this works for me: INSERT INTO first_table SELECT * FROM second_table ON DUPLICATE KEY UPDATE second_table.column1 = DO_WHAT_EVER_MUST_BE_DONE_ON_KEY_CLASH(first_table.column1) – Tapper Jun 05 '12 at 13:30
Just like @Tapper, I could not get to assign an Alias to the first table. I would get `Syntax error, unexpected IDENT_QUOTED` - through MySQL Workbench anyways. – blo0p3r Mar 10 '14 at 19:01
Could you provide an example of what command you would enter on the last line? I would like the ID to increment up from the highest existing ID in the table – Elliott B Apr 18 '18 at 20:43

score 16 · Answer 4 · edited Jun 28 '12 at 11:34

16

If you need to do it manually, one time:

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

create table MERGED as select * from table 1 UNION 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.

[edited - removed brackets in the UNION query, which was causing the error in the comment below]

edited Jun 28 '12 at 11:34

Christoph

50,121
21
99
128

answered Apr 07 '09 at 13:00

Cătălin Pitiș

14,123
2
39
62

when I try this I receieved this error, "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION select * from actor)' at line ", why is that? – jcho360 May 23 '12 at 17:20

score 7 · Answer 5 · edited Nov 17 '16 at 18:57

Not as complicated as it sounds.... Just leave the duplicate primary key out of your query.... this works for me !

INSERT INTO
  Content(
    `status`,
    content_category,
    content_type,
    content_id,
    user_id,
    title,
    description,
    content_file,
    content_url,
    tags,
    create_date,
    edit_date,
    runs
  )
SELECT `status`,
  content_category,
  content_type,
  content_id,
  user_id,
  title,
  description,
  content_file,
  content_url,
  tags,
  create_date,
  edit_date,
  runs
FROM
  Content_Images

score 0 · Answer 6 · edited Jul 10 '23 at 21:48

You could write a script to update the FK's for you.. check out this blog: http://www.multunus.com/blog/2011/03/script-to-merge-two-identical-mysql-databases/

They have a clever script to use the information_schema tables to get the "id" columns:

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;

How can I merge two MySQL tables?

6 Answers6

Linked

Related