CODEWITHSUNDEEP
mysql
Tri Nguyen
Tri Nguyen

Reputation: 11098

Merge 2 tables in MySQL with some duplicate data

I need to merge 2 tables with the same structure, table1 and table2. They have mostly different rows, but some rows are duplicates.

Is there a way to merge table2 into table1 but leave out the duplicate records in 1 statement?

I'm quite new to MySQL to any help would be greatly appreciated.

EDIT: The query I have so far is just this:

INSERT INTO table1
SELECT *
FROM table2

So I don't know how to selectively leave out the duplicates.

Upvotes: 0

Views: 3106

Answers (2)

Shawn Balestracci
Shawn Balestracci

Reputation: 7530

If you just want the results:

select * from table1 union select * from table2;

The union will remove the duplicates.

If you want to create a new table with the results:

create table merged_table
    select * from a1.t1 
    union 
    select * from a2.t1;

You can then:

drop table table1;
rename table merged_table to table1;

(Don't drop the table while other queries are or could be accessing it.)

Upvotes: 3

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28970

You can try INSERT SELECT , in order to merge table2 into table1 

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] table2 [(col_name,...)]
    SELECT * FROM table1
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

link : http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

Upvotes: 2

Related Questions