Duplicate Record Issue


What I am trying to do, is insert car item 10 into car_items if the the car's type is 0. However, since a driver can own multiple cars with the car type 0 I only need to grab the distinctive rows. Since inserting into car_items will fail because car_id and car_item_id are primary keys.

for example:

cars
car_id     driver_id     type_id
1             45               0
2             45               0
3             45               0
4             45               1
5             62               5
6             62               8
7             1                 0
8             1                 0
9             1                 0
10           1                 0

Based on the above, I should only give driver_id 1 and 45 item 10. However, the following query tries to insert 1 and 45 multiple times, which causes a duplicate key error.

INSERT car_items (driver_id, car_item_id, quantity) SELECT DISTINCT driver_id, 10, 1 FROM cars WHERE type = 0 AND driver_id NOT IN (SELECT DISTINCT driver_id FROM car_items WHERE car_item_id = 10);
plecostomusAsked:
Who is Participating?
 
SharathData EngineerCommented:
I ran your query and did not get any error. In fact I ran your INSERT statement multiple times to see if it fails in next runs. But I did not get any error. Can you post your error message?
0
 
plecostomusAuthor Commented:
Sorry, copy past error....

I am having an issue trying to insert records. Not sure what I am doing wrong, but am seeking some assistance.

CUrrently, I have two tables; cars and car_items. The table cars list all the cars owned by a specific person and the car_items table list all the items owned by the car. Here are the two tables:

CREATE TABLE cars (car_id int not null, driver_id int not null, type int not null, primary key car_id);
CREATE TABLE car_items (driver_id int not null, car_item_id int not null, quantity int not null, primary key (driver_id, car_item_id));

What I am trying to do, is insert car item 10 into car_items if the the car's type is 0. However, since a driver can own multiple cars with the car type 0 I only need to grab the distinctive rows. Since inserting into car_items will fail because car_id and car_item_id are primary keys.

for example:

cars
car_id     driver_id     type_id
1             45               0
2             45               0
3             45               0
4             45               1
5             62               5
6             62               8
7             1                 0
8             1                 0
9             1                 0
10           1                 0

Based on the above, I should only give driver_id 1 and 45 item 10. However, the following query tries to insert 1 and 45 multiple times, which causes a duplicate key error.

INSERT car_items (driver_id, car_item_id, quantity) SELECT DISTINCT driver_id, 10, 1 FROM cars WHERE type = 0 AND driver_id NOT IN (SELECT DISTINCT driver_id FROM car_items WHERE car_item_id = 10);
0
 
plecostomusAuthor Commented:
The problem appears to be due to inconsistent reads.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.