CODEWITHSUNDEEP
mysqlduplicatesinsert
dp2050
dp2050

Reputation: 342

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.

Upvotes: 0

Views: 598

Answers (3)

dp2050
dp2050

Reputation: 342

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.

Upvotes: 0

namnh
namnh

Reputation: 533

Another approach for you :

INSERT INTO your_table VALUES (SELECT * FROM table GROUP BY your_column_want_to_dupplicate);

Upvotes: 0

Vinícius Britto
Vinícius Britto

Reputation: 330

The EXISTS condition is use in a WHERE clause. In your case, the first select doesn't specify the table and the condition.

One example:

SELECT *
FROM customers
WHERE EXISTS (SELECT *
              FROM order_details
              WHERE customers.customer_id = order_details.customer_id);

Try to put your statement like this, and if it returns the data duplicated, just use a DISTINCT. (SELECT DISCTINCT * .....)

Upvotes: 0

Related Questions