how do you update rows and not violate a PK on the table?

i have the following SQL:

UPDATE Products_ProductComponents
SET Products_ProductComponents.ComponentID = products_list_ORIG.derivedFrom
FROM Products_ProductComponents
      INNER JOIN products_list_ORIG ON products_list_ORIG.ProductID = Products_ProductComponents.ComponentID
WHERE products_list_ORIG.derivedFrom <> 0

the updated sets values in the PM that already exist and i get this error:
Violation of PRIMARY KEY constraint 'PK_Products_ProductComponents'. Cannot insert duplicate key in object 'dbo.Products_ProductComponents'.

how do i get around this?
i am thi nking i can update only records that will not violate the PK
or remove the PK, then delete dups then add PK back.

ideas?
supergirl2008Asked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:
Hello,
What is the PK_Products_ProductComponents?
If it is only Products_ProductComponents.ComponentID, and If you can update only records that will not violate the PK here is the SQL:
If you need to update Products_ProductComponents.ComponentID, delete the rows and reinsert with the new values.

UPDATE Products_ProductComponents
SET Products_ProductComponents.ComponentID = products_list_ORIG.derivedFrom
FROM Products_ProductComponents
      INNER JOIN products_list_ORIG 
      ON products_list_ORIG.ProductID = Products_ProductComponents.ComponentID
WHERE products_list_ORIG.derivedFrom <> 0 and not exists (
	SELECT 1 FROM Products_ProductComponents
	WHERE Products_ProductComponents.ComponentID  =  products_list_ORIG.derivedFrom
)

Open in new window

0
 
UnifiedISCommented:
"remove the PK, then delete dups then add PK back"
This wouldn't work, you can not add a key if it would violate the key you are adding.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.