Solved

Duplicate Record Issue

Posted on 2011-03-05
3
263 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
  • 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 40

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Duplicating MySQL Table columns 5 45
Trigger usage 2 75
myqsl update statement on phpMyAdmin 8 37
update joined tables 2 55
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 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