Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Duplicate Record Issue

Posted on 2011-03-05
3
Medium Priority
?
272 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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 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