Using sql server 2012. I'm trying to return a list of orders along with a count of rows in another table. I've succeeded in returning a count of the very first order for all orders instead of individual counts. The problem lies in the UPDATE sql but I'm not sure what the syntax is.
DECLARE @SubmittedOrders AS TABLE(
[CustID] varchar(10)
,[OrderID] varchar(15)
,[DateSubmitted] datetime
,[CabinetCount] integer
)
INSERT INTO @SubmittedOrders
SELECT CustID, OrderID, E, 0
FROM tableOO
WHERE (A = 'SUBMITTED')
UPDATE @SubmittedOrders
SET CabinetCount =
(
SELECT COUNT(*) AS cabCount
FROM tableDDDD d
INNER JOIN tableOO o ON o.OrderID = d.OrderID
WHERE (d.LineType = 'LC') AND (o.A = 'SUBMITTED')
)
select * from @SubmittedOrders
The above is currently returning these results. The value of the cabinet count for the first order is being returned for all orders instead of for each order.

Thanks for your help.