SQL Statement with multiple updates

I have two updates to two different tables that need to happen in one stored procedure. The first update relies on the second update not happening until the first update has run. I can get them both to work accurately when run independent of each other however together the only one that works is the second one. Any help would be gratefully appreciated.
UPDATE Inventory
SET Quantity=i.Quantity-p.Quantity FROM PO_Hist p, Inventory i
WHERE i.Description=p.ProdDescr AND p.AM_PM = 'PM' AND p.Date=Convert(date, GETDATE()) AND p.SubDept='Grocery' AND p.Submitted=NULL
GO
UPDATE PO_Hist
SET Submitted=1 FROM PO_Hist p, Inventory i
WHERE i.Description=p.ProdDescr AND p.AM_PM = 'PM' AND p.Date=Convert(date, GETDATE()) AND p.SubDept='Grocery'

Open in new window

IScoopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE Inventory
SET Quantity=i.Quantity-p.Quantity FROM PO_Hist p, Inventory i
WHERE i.Description=p.ProdDescr AND p.AM_PM = 'PM' AND p.Date=Convert(date, GETDATE()) AND p.SubDept='Grocery' AND p.Submitted=NULL
if @@ROWCOUNT > 0
UPDATE PO_Hist
SET Submitted=1 FROM PO_Hist p, Inventory i
WHERE i.Description=p.ProdDescr AND p.AM_PM = 'PM' AND p.Date=Convert(date, GETDATE()) AND p.SubDept='Grocery'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris LuttrellSenior Database ArchitectCommented:
your problem with the first statement is that you used "p.Submitted=NULL" instead of "p.Submitted IS NULL"  you can not use =, <, >, etc with a NULL you have to use IS NULL or IS NOT NULL.

UPDATE Inventory
SET Quantity=i.Quantity-p.Quantity FROM PO_Hist p, Inventory i
WHERE i.Description=p.ProdDescr AND p.AM_PM = 'PM' AND p.Date=Convert(date, GETDATE()) AND p.SubDept='Grocery' AND p.Submitted IS NULL

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.