CODEWITHSUNDEEP
mysql
Jossy
Jossy

Reputation: 999

Why am I getting "Duplicate entry" error on SELECT DISTINCT query?

I have the following query to append data into a table if it is unique:

INSERT INTO belgarath.players(tour_id, player_id, player_name_oc)
SELECT DISTINCT 0, ID_P, NAME_P FROM oncourt.players_atp
LEFT JOIN belgarath.players
    ON belgarath.players.tour_id = 0
    AND belgarath.players.player_id=oncourt.players_atp.ID_P;

I run this once on an empty table and it's fine. I delete a row and run it expecting MySQL to append the one deleted row. However, I get the following error code: Error Code: 1062. Duplicate entry '0-43042' for key 'players.unique_plyrs' . I have a unique key across tour_id and player_id and clearly it's failing because I'm trying to append a duplicate record.

Why would I be getting this if I'm only selecting distinct records to insert? How do I avoid getting this in future?

Upvotes: 0

Views: 635

Answers (2)

abhijeet chavan
abhijeet chavan

Reputation: 68

Hope this hint realted to Distinct keyword helps you. When we use distinct key it usually select distinct rows. So we can't expect it should return distinct values for only one column before which we have wrote distinct. Below example will better explain you what i am trying to say.

create table test(id1 int, id2 int);
insert into test values(1,1),(1,2),(1,3);

Here i have created a test table and when i use distinct keyword as used in below query

select distinct id1, id2 from test;

Then we'll get output like this:

id1 id2

1 1

1 2

1 3

You are inserting tour_ID as 0, and as you have defined tour_id and player_id as unique key in oncourt.players_atp table. So your select query is selecting tour_id as '0' every time. Because select query with distinct is getting really distinct records like say player_id is 1,2,3 and names are john, steve, bill respectively then select query will return this 3 records like (0, 1, john), (0, 2, steve), (0, 3, bill) and so on.

If your oncourt.players_atp table also has unique constraint and that table also contains tour_id then you can just copy tour ID from there. If tour_id is not present there and you want to generate it inside belgarath.players table only then in you table definition you can define tour id as a auto increment then it will generate unique id's there and then you don't need to select tour_id in your query you just have to insert player_id and player_name once you define tour_id as an autoincrement ID. Hope this may help you.

Upvotes: 0

SS_DBA
SS_DBA

Reputation: 2423

This should resolve your issue. Put a Where clause to check for belgarath.players.player_id is NULL.

INSERT INTO belgarath.players(tour_id, player_id, player_name_oc)
SELECT DISTINCT 0, ID_P, NAME_P FROM oncourt.players_atp
LEFT JOIN belgarath.players
    ON belgarath.players.tour_id = 0
    AND belgarath.players.player_id=oncourt.players_atp.ID_P
WHERE belgarath.players.player_id is NULL;

Upvotes: 1

Related Questions