Solved

Duplicate Record Issue

Posted on 2011-03-05
3
265 Views
Last Modified: 2012-05-11

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
Comment
Question by:plecostomus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:plecostomus
ID: 35044681
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
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35044787
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
 

Author Closing Comment

by:plecostomus
ID: 35059662
The problem appears to be due to inconsistent reads.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question