Balaji Kandasamy Balaji Kandasamy - 4 months ago 8
SQL Question

How to ignore inserting records from a table to another table when both table field has same value in MySQL?

I have 2 tables which have names and emails. Now I want to merge these tables into a new table without duplicate records. I want to use email fields for avoiding duplicate values in both tables. I heard INSERT IGNORE query is using for inserting values into a table without affecting existing records. How to write INSERT IGNORE query to check email field for duplication checking. If anyone knows other methods are also welcome.

table1:
fid fname email
--- ----- -----
1 Balaji balaji@email.com
2 xxxxx xxxxx@email.com
3 Bala bala@email.com

table2:

gid gname gemail
--- ----- ------
1 Bala bala@email.com
2 vinoth vinoth@email.com


Expected result:



table3:
-------
id name email
-- ---- -----
1 Balaji balaji@email.com
2 xxxxx xxxxx@email.com
3 Bala bala@email.com
4 vinoth vinoth@email.com

Answer

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

Assuming Table3 is the name of your new table. You need to add constraint first,

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

and you can now have unique records on the new table, to merge 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);

An alternative solution without using ON DUPLICATE KEY UPDATE. is to use UNION (without ALL) and assumes that 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