CODEWITHSUNDEEP
mysqlsqlreplaceinsert
Julien
Julien

Reputation: 415

How to merge 2 tables in MySQL?

I would like merge 2 MySQL tables without adding the same lines. I have 2 tables with the sames columns but not the same row, such as.

table_1
+-------+--------+------+
| Name  | ResNum | Code | 
+-------+--------+------+
| User1 | 25BAM8 | PAR1 |
| User2 | E26J09 | COP3 |
+-------+--------+------+

table_2
+-------+--------+------+
| Name  | ResNum | Code | 
+-------+--------+------+
| User1 | 25BAM8 | PAR1 |
| User3 | 34VNS2 | PAR1 |
| User4 | EZQVG5 | COP3 |
+-------+--------+------+

I want when merging table_2 to table_1, got this:

table_1
+-------+--------+------+
| Name  | ResNum | Code | 
+-------+--------+------+
| User1 | 25BAM8 | PAR1 |
| User2 | E26J09 | COP3 |
| User3 | 34VNS2 | PAR1 |
| User4 | EZQVG5 | COP3 |
+-------+--------+------+

I have already tried with INSERT IGNORE INTO and REPLACE INTO command but it copy the existing rows, like that:

table_1
+-------+--------+------+
| Name  | ResNum | Code | 
+-------+--------+------+
| User1 | 25BAM8 | PAR1 |
| User1 | 25BAM8 | PAR1 |
| User2 | E26J09 | COP3 |
| User3 | 34VNS2 | PAR1 |
| User4 | EZQVG5 | COP3 |
+-------+--------+------+

Any help would be appreciated.

Upvotes: 0

Views: 201

Answers (3)

Teja
Teja

Reputation: 13534

The below query should work fine.

SELECT * FROM TABLE_1 T1,TABLE_2 T2
WHERE T1.NAME = T2.NAME
UNION
SELECT * FROM TABLE_1 T1, TABLE_2 T2
WHERE T1.NAME != T2.NAME

Upvotes: 1

alexn
alexn

Reputation: 58992

An easy solution would be to put a temporary UNIQUE index on Name on table_1:

CREATE UNIQUE INDEX unique_name ON table_1(Name)

To transfer the actual rows, use a SELECT INSERT:

INSERT INTO table_1(Name, ResNum, Code) SELECT Name, ResNum, Code FROM table_2.

Then drop the UNIQUE index:

DROP INDEX unique_name ON table_1

Upvotes: 3

Brad
Brad

Reputation: 163438

You need to set a UNIQUE key, if uniqueness in those fields is important to you.

Then you can do an INSERT ... SELECT without problem, and you won't run into issues with this in the future.

Upvotes: 3

Related Questions