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

Question

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

http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — didierc, Jun 25 '13 at 05:29
did you found solution for this : http://magento.stackexchange.com/questions/5571/is-there-any-free-extension-available-for-designing-products-label-or-cover-in-m/%22Here%22? , if so please help me with complete code, can you join here : http://chat.stackexchange.com/rooms/42832/magento-discuss — fresher, Aug 02 '16 at 11:12

John Woo · Accepted Answer · 2013-06-25T05:48:24.120

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

SQLFiddle Demo

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

Expected result:

1 Answers1