Sql-server – Update primary key column – NULL issue

sql serversql-server-2008

I want to update a column that is a primary key using this code 

use BITS_TEE

Update DMT_TEE_DTS_Production
set ABCIndicatorCode =  
    (
    select HIST.ABCIndicatorCode
    from bits.dbo.REF_MaterialPlantHistory HIST
    where 
    HIST.MaterialPlantCode = DMT_TEE_DTS_Production.MaterialPlantCode
    and convert(nvarchar(8),HIST.StartDate, 112) <= DMT_TEE_DTS_Production.DayId
    and DMT_TEE_DTS_Production.DayId <= convert(nvarchar(8),HIST.EndDate, 112)
    )

It returns this error:

Msg 515, Level 16, State 2, Line 3 Cannot insert the value NULL into
column 'ABCIndicatorCode', table
'BITS_TEE.dbo.DMT_TEE_DTS_Production'; column does not allow nulls.
UPDATE fails. The statement has been terminated.

The problem is there is no NULL in bits.dbo.REF_MaterialPlantHistory.ABCIndicatorCode

Please help me!

Description of DMT_TEE_DTS_Production table:

[DayId] [decimal](8, 0) NOT NULL,
[MaterialCode] [nvarchar](18) NOT NULL,
[CompanyCode] [nvarchar](4) NOT NULL,
[PlantStorageLocationCode] [nvarchar](9) NOT NULL,
[BaseUnitCode] [nvarchar](3) NOT NULL,
[ABCIndicatorCode] [nvarchar](2) NOT NULL,
[OriginalMaterialStatusCode] [nvarchar](2) NOT NULL,
[ProductionMvtTypeCode] [nvarchar](3) NOT NULL,
[BTCShiftCode] [nvarchar](4) NOT NULL,
[BTCStandardRollIndicator] [nvarchar](2) NOT NULL,
[BTCDefectCode] [nvarchar](11) NOT NULL,
[BTCOrderNumber] [nvarchar](12) NOT NULL,
[TEEBigRollIndicator] [nvarchar](12) NOT NULL,
[COR_Quantity] [decimal](20, 6) NULL,
[COR_Length] [decimal](20, 6) NULL,
[COR_StandardCost_GrpCur] [decimal](20, 6) NULL,
[COR_Weight] [decimal](20, 6) NULL,
[MaterialPlantCode] [nvarchar](30) NULL,
[PlantMaterialStatusCode] [nvarchar](2) NULL,
PRIMARY KEY CLUSTERED 
(
    [DayId] ASC,
    [MaterialCode] ASC,
    [CompanyCode] ASC,
    [PlantStorageLocationCode] ASC,
    [BaseUnitCode] ASC,
    [ABCIndicatorCode] ASC,
    [OriginalMaterialStatusCode] ASC,
    [ProductionMvtTypeCode] ASC,
    [BTCShiftCode] ASC,
    [BTCStandardRollIndicator] ASC,
    [BTCDefectCode] ASC,
    [BTCOrderNumber] ASC,
    [TEEBigRollIndicator] ASC

rows: 

select DayId, MaterialPlantCode, ABCIndicatorCode 
from DMT_TEE_DTS_Production

20130902    -1  D
20130918    -1  E
20130919    -1  X
20130919    -1  A
20130919    -1  E
20130921    -1  X
20130921    -1  -1
20130922    -1  B
20130922    -1  -1
20130922    -1  X
20130924    -1  -1
20130924    -1  -1
20130924    -1  B
20130924    -1  B
20130924    -1  C

Description of REF_MaterialPlantHistory table:

[MaterialPlantCode] [nvarchar](30) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[MaterialCode] [nvarchar](18) NULL,
[PlantCode] [nvarchar](4) NULL,
[MaterialStatusCode] [nvarchar](2) NULL,
[ABCIndicatorCode] [nvarchar](2) NULL,
[ABCConsoIndicatorCode] [nvarchar](2) NULL,
[ABCStockIndicatorCode] [nvarchar](2) NULL,
[ABCSalesMarketIndicatorCode] [nvarchar](2) NULL,
[ProcessDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [MaterialPlantCode] ASC,
    [StartDate] ASC

rows:

MaterialPlantCode   StartDate   EndDate MaterialCode    PlantCode   MaterialStatusCode  ABCIndicatorCode
1000000000-1400 1900-01-01 00:00:00.000 2014-07-17 00:00:00.000 1000000000  1400    1   
1000000000-1400 2014-07-18 00:00:00.000 2999-12-31 00:00:00.000 1000000000  1400    11  Z
100000002-0019  1900-01-01 00:00:00.000 2014-07-17 00:00:00.000 100000002   0019    0   B
100000002-0019  2014-07-18 00:00:00.000 2999-12-31 00:00:00.000 100000002   0019    11  Z
1000000-0310    1900-01-01 00:00:00.000 2014-07-17 00:00:00.000 1000000 0310    0   
1000000-0310    2014-07-18 00:00:00.000 2999-12-31 00:00:00.000 1000000 0310    11  Z
1000000-0320    1900-01-01 00:00:00.000 2014-07-17 00:00:00.000 1000000 0320    0   
1000000-0320    2014-07-18 00:00:00.000 2999-12-31 00:00:00.000 1000000 0320    10  
100000004-0019  1900-01-01 00:00:00.000 2999-12-31 00:00:00.000 100000004   0019    2   C
100000006-0019  1900-01-01 00:00:00.000 2999-12-31 00:00:00.000 100000006   0019    0   B
100000007-0019  1900-01-01 00:00:00.000 2999-12-31 00:00:00.000 100000007   0019    0   B
100000008-0019  1900-01-01 00:00:00.000 2999-12-31 00:00:00.000 100000008   0019    0   B
100000009-0019  1900-01-01 00:00:00.000 2999-12-31 00:00:00.000 100000009   0019    0   B
100000011-0019  1900-01-01 00:00:00.000 2999-12-31 00:00:00.000 100000011   0019    0   B

Best Answer

The correlated subquery acts like a LEFT join between the 2 tables. If there is no row in the REF_MaterialPlantHistory matching the WHERE conditions, the subquery returns a NULL and this yields the error.

Try using INNER join:

UPDATE prod
SET ABCIndicatorCode = hist.ABCIndicatorCode
FROM 
    DMT_TEE_DTS_Production AS prod
  JOIN 
    bits.dbo.REF_MaterialPlantHistory AS hist
      ON  hist.MaterialPlantCode = prod.MaterialPlantCode
      AND convert(nvarchar(8), hist.StartDate, 112) <= prod.DayId
      AND prod.DayId <= convert(nvarchar(8), hist.EndDate, 112) ;
Related Question