Solved

Duplicate Record Issue

Posted on 2011-03-05
3
268 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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