0

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
  • 1
    Your subquery will be returning NULL. Fix the subquery and you solve your problem – Mark Sinkinson Jul 29 '14 at 9:22
  • The join is based on the MaterialPlantCode and the dates columns. You haven't shown the MaterialPlantCode column in the first table. My guess is that some rows have no value in that column or that there not exists a relative row in the history table (relative as having same materialplantcode and compatible dates) – ypercubeᵀᴹ Jul 29 '14 at 10:32
  • So, I in your edit several rows in the production table with MaterialPlantCode = -1. Are there rows in the history table with such a code? – ypercubeᵀᴹ Jul 29 '14 at 12:30
  • NO, the -1 values it's when there are no matchs – nina Jul 29 '14 at 12:37
3

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) ;
  • OK it worked but... it updated only 200 rows and i have 1437 rows – nina Jul 29 '14 at 9:51
  • Then for the rest (1237) rows in production table, there are no relevant rows in the history table. Or the conversion conditions are not correct. Can you edit the question and add the table definitions and a few sample rows from both tables? (only the columns involved in the query will be needed) – ypercubeᵀᴹ Jul 29 '14 at 9:56
  • i tried to make it as a table ... but lol it doesn't work any way wish it will be clear :) – nina Jul 29 '14 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.