SQL Update Statement with Multiple Selects
I get the following message from SQL Server 2008:
"Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'PrimVendID', table 'bzbapp.dbo.ItemSite'; column does not allow nulls. UPDATE fails.
The statement has been terminated."
When I run the following Query:
UPDATE
ITEMSITE
SET
ITEMSITE.PRIMVENDID=
(SELECT TOP 1 POTran.vendid
FROM POTran
where POTRAN.invtid = ITEMSITE.INVTID
AND POTran.trandate>='06/23/20
ORDER BY POTran.PONbr DESC)
WHERE
ITEMSITE.SITEID='WEST'
AND itemsite.invtid in (Select itemsite.invtid from itemsite where itemsite.siteid='WEST' and itemsite.moveclass='A')
I am trying to update the itemsite table's PRIMVENDID utilizing the POTRAN tables most recent record VENDID field.
Your input on how to format this QUERY would be greatly appreciated. I cannot seem to figure out how I am picking up a null value.
"Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'PrimVendID', table 'bzbapp.dbo.ItemSite'; column does not allow nulls. UPDATE fails.
The statement has been terminated."
When I run the following Query:
UPDATE
ITEMSITE
SET
ITEMSITE.PRIMVENDID=
(SELECT TOP 1 POTran.vendid
FROM POTran
where POTRAN.invtid = ITEMSITE.INVTID
AND POTran.trandate>='06/23/20
ORDER BY POTran.PONbr DESC)
WHERE
ITEMSITE.SITEID='WEST'
AND itemsite.invtid in (Select itemsite.invtid from itemsite where itemsite.siteid='WEST' and itemsite.moveclass='A')
I am trying to update the itemsite table's PRIMVENDID utilizing the POTRAN tables most recent record VENDID field.
Your input on how to format this QUERY would be greatly appreciated. I cannot seem to figure out how I am picking up a null value.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/A_1517.html