• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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/2010'
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.
0
armgon
Asked:
armgon
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
ITEMSITE.PRIMVENDID=
(SELECT  TOP 1 POTran.vendid
FROM POTran
where POTRAN.invtid = ITEMSITE.INVTID
AND POTran.trandate>='06/23/2010' 
ORDER BY POTran.PONbr DESC)

Open in new window

If this sub-query is not having any result, NULL will be returned by sub-query. that could be the cause of the crash.

Modify that part to handle NULL scenarios.
ITEMSITE.PRIMVENDID=
ISNULL((SELECT  TOP 1 POTran.vendid
FROM POTran
where POTRAN.invtid = ITEMSITE.INVTID
AND POTran.trandate>='06/23/2010' 
ORDER BY POTran.PONbr DESC), 0) 

Open in new window

What should be the value if NULL ? If you consider 0, if can try the above correction. it will save zero, if sub-query is not returning any value

Raj
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
would this article help you writing your UPDATE with JOINs?
http://www.experts-exchange.com/A_1517.html
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now