CODEWITHSUNDEEP
mysqlselectinsertduplicates
Zak
Zak

Reputation: 7525

MySQL - Combine two tables into third without duplicates

I am having a hard time wrapping my mind around a concept here. What I have are three tables that are identical structure. I need table 1 to be combined with table 2 and INSERT INTO table 3. My issue:

table 1 and table 2 have duplicate content, EXCEPTION BEING their ID. I am not using the ID for anything other than AI however, so that really is not a concern.

An example of what I am looking to accomplish is:

                        original TABLE 1
 _________________________________________________________
|    ID   |      Col 2     |     Col 3    |      Col 4    |
|---------------------------------------------------------|
|   1     |     stuff_1    |     stuff_2  |     stuff_3   |
|---------------------------------------------------------|
|   2     |     stuff_x    |     stuff_y  |     stuff_z   |
|_________________________________________________________|

Combine with

                        original TABLE 2
 _________________________________________________________
|    ID   |      Col 2     |     Col 3    |      Col 4    |
|---------------------------------------------------------|
|   3     |     stuff_1    |     stuff_2  |     stuff_3   |
|---------------------------------------------------------|
|   4     |     stuff_a    |     stuff_b  |     stuff_c   |
|_________________________________________________________|

To make

combined TABLE 3 (notice ID's are not counted in duplicate check)
 _________________________________________________________
|    ID   |      Col 2     |     Col 3    |      Col 4    |
|---------------------------------------------------------|
|   ?     |     stuff_1    |     stuff_2  |     stuff_3   |
|---------------------------------------------------------|
|   ?     |     stuff_x    |     stuff_y  |     stuff_z   |
|---------------------------------------------------------|
|   ?     |     stuff_a    |     stuff_b  |     stuff_c   |
|_________________________________________________________|

I have tried insert into table 3 select * from table 2; and THEN doing a insert ignore from table 1. I've tried replace into as well, but because I am omitting the ID, those don't work because the column count is off. Does anyone have any ideas how to combine these two into the third table while dealing with the ID issue? I will reiterate that it really does not matter what the ID's are as they will not ever be used.

Upvotes: 2

Views: 6369

Answers (3)

Brian Moore
Brian Moore

Reputation: 276

The Goal is to have the contents of Table1 Inserted into Table3, and the contents of Table2, which are not in Table1, to be inserted into Table3. The following query does exactly that.

INSERT INTO Table3 (Col1, Col2, Col3)
SELECT Col1,
       Col2,
       Col3
  FROM Table1
UNION
SELECT Col1,
       Col2,
       Col3
  FROM Table2
  WHERE (Col1, Col2, Col3) NOT IN (SELECT Col1, Col2, Col3 FROM Table 1);

Upvotes: 0

mogul
mogul

Reputation: 4553

I added an unique index on col2, col3, col4 on table3 

alter table t3 add unique u1 (col2, col3, col4);

and then did

insert ignore into t3 select * from t1 union select * from t2;

to get

+------+---------+---------+---------+
| id   | col2    | col3    | col4    |
+------+---------+---------+---------+
|    1 | stuff_1 | stuff_2 | stuff_3 |
|    2 | stuff_x | stuff_y | stuff_z |
|    4 | stuff_a | stuff_b | stuff_c |
+------+---------+---------+---------+

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26353

You can use GROUP BY to flatten out the duplicates while leaving an ID value.

This will give you unique Col1, Col2 and Col3 values, and will set the ID to the minimum ID value for any rows that were merged as duplicated:

INSERT INTO Table3 (ID, Col1, Col2, Col3)
SELECT MIN(ID), Col1, Col2, Col3
FROM (
  SELECT ID, Col1, Col2, Col3 FROM Table1
  UNION SELECT ID, Col1, Col2, Col3 FROM Table2
) Table1And2
GROUP BY Col1, Col2, Col3

With your posted data, the result should be this:

ID    Col1     Col2     Col3
--    -------  -------  -------
 1    stuff_1  stuff_2  stuff_3
 2    stuff_a  stuff_b  stuff_c
 4    stuff_x  stuff_y  stuff_z

Upvotes: 2

Related Questions