Search code examples
mysqljoinleft-joininner-joincrud

MYSQL: How to avoid inserting duplicate records?

I am making an insert statement as following:

Insert into table2 (host, ip, domain, statusnew) 
select hostname, ip, domain, "True" from table1 t1, table2 t2 
where t1.status = "Done"
and t2.statusnew not regexp "True"
limit 10
;

I have added the statement t2.statusnew not regexp "True" just to make sure that there is no duplicate insertion. But, it is adding duplicate rows.

How to make sure that there is no duplicate entries?

Solution 
  • INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE NOT EXISTS(SELECT id FROM TABLE_2 t2 WHERE t2.id = t1.id)