Update Statement Fails

Question

I have a table called [dbo].[ProductComponentRelationship] with 4 fields

[ProductComponentRelationshipID] PK, INt, Not Null
[ProductItemID] FK, Int Not Null
[ComponentItemID] FK, Int, Not Null
[SequenceNumber] int null

This table houses a bunch of values. I need to update 4000 records in the table above. Therefore i populated a seperate table with a productItemID, and the new ComponentitemID value. I tried to run the sql statement below and it failed:

update ProductComponentRelationship set ComponentItemID = 
(select compid from cst_pricefix where 
ProductComponentRelationship.ProductItemID = cst_pricefix.prditem and 
ProductComponentRelationship.ProductComponentRelationshipID = ProductComponentRelationship.ProductComponentRelationshipID )

Error Message: 
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ComponentItemID', table 'SDSDB.dbo.ProductComponentRelationship'; column does not allow nulls. UPDATE fails.

Seems straight forward. You are setting that field to null and it fails because it cannot be set to null. Does this statement return anything? select compid from cst_pricefix where ProductComponentRelationship.ProductItemID = cst_pricefix.prditem and ProductComponentRelationship.ProductComponentRelationshipID = ProductComponentRelationship.ProductComponentRelationshipID — Twelfth, Sep 17 '14 at 22:31

score 1 · Answer 1 · answered Sep 17 '14 at 22:32

If this is SQL Server, which it looks like from the dbo, you can update over a join like so:

update
    pcr
set
    ComponentItemID = f.compid
from
    ProductCompnentRelationship pcr
        inner join
    cst_pricefix f
        on pcr.ProductItemID = f.prditem

I'm not sure what ProductComponentRelationship.ProductComponentRelationshipID = ProductComponentRelationship.ProductComponentRelationshipID was about in your original query, so there may be something missing from mine.

Update Statement Fails

1 Answers1