How to ignore inserting records from a table into another table when both fields of the table have the same value in MySQL?

Question

I have 2 tables with names and emails. Now I want to merge these tables into a new table without duplicate records. I want to use email fields to avoid duplicate values ​​in both tables. I heard that the INSERT IGNORE query is used to insert values ​​into a table without affecting existing records. How to write an INSERT IGNORE query to check the email field to check for duplication. If someone knows that other methods are also welcome.

table1: fid fname email --- ----- ----- 1 Balaji [e-mail] 2 xxxxx [e-mail] 3 Bala [e-mail] table2: gid gname gemail --- ----- ------ 1 Bala [e-mail] 2 vinoth [e-mail] 

Expected Result:

 table3: ------- id name email -- ---- ----- 1 Balaji [e-mail] 2 xxxxx [e-mail] 3 Bala [e-mail] 4 vinoth [e-mail] 

Answer 1

MySQL support UPDATE ON DUPLICATE KEY , but to work you need to add a unique constraint to the table you want to insert.

Assuming Table3 is the name of your new table. First you need to add a constraint,

 ALTER TABLE Table3 ADD CONSTRAINT tb_uq UNIQUE (name, email) 

and now you can have unique records in the new table to combine the previous table,

 INSERT INTO table3(name, email) SELECT name, email FROM ( SELECT fid id, fname name, email FROM Table1 UNION ALL SELECT gid id, gname name, gemail email FROM Table1 ) s ON DUPLICATE KEY UPDATE name = VALUES(name); 

• SQLFiddle Demo
• INSERT ... ON DUPLICATE KEY UPDATE Syntax

---

Alternative solution without using ON DUPLICATE KEY UPDATE . consists in using UNION (without ALL) and assumes Table3.ID is set as auto-increment

 INSERT INTO table3(name, email) SELECT name, email FROM ( SELECT fname name, email FROM Table1 UNION SELECT gname name, gemail email FROM Table2 ) s 

• SQLFiddle Demo