February 19, 2008 at 1:04 pm
I am having a little problem with my update query. I get the following Error msg.
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ITEMDESC', table 'ATI.dbo.IV00101'; column does not allow nulls. UPDATE fails.
Here is my script, I would be grateful for any suggestions;
UPDATE [ATI].[dbo].[IV00101]
SET ITEMDESC =(SELECT ITEMDESC FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
ITMSHNAM =(SELECT ITMSHNAM FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
ITEMTYPE =(SELECT ITEMTYPE FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
ITMGEDSC =(SELECT ITMGEDSC FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
UOMSCHDL =(SELECT UOMSCHDL FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
USCATVLS_1 =(SELECT USCATVLS_1 FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
USCATVLS_2 =(SELECT USCATVLS_2 FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
USCATVLS_3 =(SELECT USCATVLS_3 FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR])
February 19, 2008 at 1:14 pm
fred (2/19/2008)
I am having a little problem with my update query. I get the following Error msg.Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ITEMDESC', table 'ATI.dbo.IV00101'; column does not allow nulls. UPDATE fails.
Here is my script, I would be grateful for any suggestions;
UPDATE [ATI].[dbo].[IV00101]
SET ITEMDESC =(SELECT ITEMDESC FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
ITMSHNAM =(SELECT ITMSHNAM FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
ITEMTYPE =(SELECT ITEMTYPE FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
ITMGEDSC =(SELECT ITMGEDSC FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
UOMSCHDL =(SELECT UOMSCHDL FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
USCATVLS_1 =(SELECT USCATVLS_1 FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
USCATVLS_2 =(SELECT USCATVLS_2 FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]),
USCATVLS_3 =(SELECT USCATVLS_3 FROM [AMCHR].[dbo].[IV00101] WHERE [AMCHR].[dbo].[IV00101].[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR])
First, why not this?
UPDATE [ATI].[dbo].[IV00101]
SET ITEMDESC = x.ITEMDESC
,ITMSHNAM = x.ITMSHNAM
....
FROM [AMCHR].[dbo].[IV00101] AS x
WHERE x.[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]
Second, you must have null values in the [AMCHR].[dbo].[IV00101].[ITEMDESC] column.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2008 at 1:23 pm
Thanks for the tighter code sample, I looked in the table the only NULL value is the very last row in the table and all the fields are NULL would it not ignore the last row, how would I exclude the NULL from my Query, I have tried adding the NOT NULL statement but I must not be adding it in the proper sequence.
February 19, 2008 at 1:26 pm
Yeah, it only takes one row.
Using my code sample
UPDATE [ATI].[dbo].[IV00101]
SET ITEMDESC = x.ITEMDESC
,ITMSHNAM = x.ITMSHNAM....
FROM [AMCHR].[dbo].[IV00101] AS x
WHERE x.[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]
AND x.ITEMDESC IS NOT NULL
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2008 at 1:53 pm
Grant,
Thanks a bunch, I rewrote the code in your format and it ran without the Null error even without the NOT NULL statement.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply