By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

UPDATE table but dissallow duplicates

code green
Expert 100+
P: 1,726
I have a table `product_part` of two fields - `product` and `part_no`. A `product` can have multiple parts and `part_no` can fit multiple products. This is a key table that links to other tables. Each field is a key field and no duplicates are allowed in the table. Fine. But some part numbers have been incorrectly inserted. I need to UPDATE this table to the correct part number. So I have created a temporary table 'new_part_no` holding the incorrect and correct `part_no`. I tried this query
Expand|Select|Wrap|Line Numbers
  1. UPDATE `product_part`,`new_part_no` SET `product_part`.`part_no` = 
  2. `'new_part_no`.`part_no` WHERE  
  3. `product_part`.`part_no` = `new_part_no`.`wrong_part_no` 
But this fails because correcting the `part_no` creates duplicates which are dissallowed. Can anybody modify this query so that I can UPDATE all the records but DELETE any records that would result in a duplicate being created.
Apr 26 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Not sure of the logic of what you are doing but if you create a left join on the part_no and only update the fields where join results in a null set. Excuse any syntax error as MySQL is not my area.

Expand|Select|Wrap|Line Numbers
  1. UPDATE `new_part_no` LEFT JOIN `product_part` 
  2. ON `new_part_no`.`part_no` = `product_part`.`part_no`
  3. SET `product_part`.`part_no` = `new_part_no`.`part_no` 
  4. WHERE  `product_part`.`part_no` Is Null
  5.  
Mary
Apr 28 '07 #2

code green
Expert 100+
P: 1,726
Thanks for the reply. I solved this partly by hand. I disabled the primary/unique key index then updated `product_part`
Expand|Select|Wrap|Line Numbers
  1. UPDATE `product_part`,`new_part_no` SET `product_part`.`part_no` = 
  2. `new_part_no`.`part_no` WHERE  
  3. `product_part`.`part_no` = `new_part_no`.`wrong_part_no` 
So now `product_part` contained unwanted duplicates. Then I added an auto-increment field `auto` and deleted all duplicates with
Expand|Select|Wrap|Line Numbers
  1. DELETE `product_part` A FROM `product_part` A, `product_part` B 
  2. WHERE A.`part_no` = B.`part_no` AND 
  3. A.`product` = B.`product`
  4. AND A.`auto` > B.`auto`
So the newer duplicates were deleted and the part codes were updated. I then removed the `auto` field and re-instated the primary keys with
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE `product_part`
  2. ADD PRIMARY KEY (`part_no`,`product`)
Thanks again
May 1 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for the reply. I solved this partly by hand. I disabled the primary/unique key index then updated `product_part`
Expand|Select|Wrap|Line Numbers
  1. UPDATE `product_part`,`new_part_no` SET `product_part`.`part_no` = 
  2. `new_part_no`.`part_no` WHERE  
  3. `product_part`.`part_no` = `new_part_no`.`wrong_part_no` 
So now `product_part` contained unwanted duplicates. Then I added an auto-increment field `auto` and deleted all duplicates with
Expand|Select|Wrap|Line Numbers
  1. DELETE `product_part` A FROM `product_part` A, `product_part` B 
  2. WHERE A.`part_no` = B.`part_no` AND 
  3. A.`product` = B.`product`
  4. AND A.`auto` > B.`auto`
So the newer duplicates were deleted and the part codes were updated. I then removed the `auto` field and re-instated the primary keys with
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE `product_part`
  2. ADD PRIMARY KEY (`part_no`,`product`)
Thanks again
You're welcome
May 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.