Search code examples
mysqlduplicatesinsert

Duplicate records in MySQL. EXISTS check for the same data not working properly?

SELECT EXISTS 
(
    SELECT *
    FROM table
    WHERE deleted_at IS NULL
    AND the_date = '$the_date'
    AND company_name = '$company_name'
    AND purchase_country = '$p_country'
    AND lot = '$lot_no'
) AS numofrecords")

What is wrong with this MySQL query? It is still allowing duplicates inserts (1 out of 1000 records). Around 100 users making entries, so the traffic is not that big, I assume. I do not have access to the database metrics, so I can not be sure.

Solution

  • The answer from @Nick gave the clues to solve the issue. Separated EXIST check and INSERT was not the best way. Two users were actually able to do INSERT, if one got 0. A single statement query with INSERT ... ON DUPLICATE KEY UPDATE... was the way to go.