0

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.

Tri Nguyen
  • 9,950
  • 8
  • 40
  • 72
  • should we guess your queries ? – echo_Me Apr 20 '13 at 19:15
  • @echo_me He talks about a principle, I think that there is no need for queries in his question. – SaidbakR Apr 20 '13 at 19:17
  • @echo_me I'm not exactly sure what the query should be. The only thing I have so far is `INSERT INTO table1 SELECT * FROM table2`, but I don't know how to leave out the duplicate records. – Tri Nguyen Apr 20 '13 at 19:19
  • 2
    You may have a good reference at this one: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – xsigndll Apr 20 '13 at 19:21

2 Answers2

3

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.)

vie
  • 15
  • 6
Shawn Balestracci
  • 7,380
  • 1
  • 34
  • 52
  • how do I get this to check for duplicates from certain columns? Each table currently have their own IDs, so comparing using ID would not result in any duplicates. – Tri Nguyen Apr 20 '13 at 19:34
2

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

Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51
  • how does this deal with the duplicates? – Tri Nguyen Apr 20 '13 at 19:22
  • If I had the `ID` column of these tables to `AUTO_INCREMENT` and then assigned that as the `PRIMARY KEY`, is there a way to check for duplicate using other columns during this process? – Tri Nguyen Apr 20 '13 at 19:33