Unable to update temp table with Count from 2 tables with inner join

Question

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.

Answer 1

Hi @EM ,

Welcome to Microsoft Q&A!

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data and the expected result of the sample after updating.

Please have a try with below update statement and check whether it is helpful.

update a  
set a.[CabinetCount]=b.cabCount  
from @SubmittedOrders a  
inner join   
(SELECT d.OrderID ,COUNT(*) AS cabCount  
FROM tableDDDD d  
INNER JOIN tableOO o ON o.OrderID = d.OrderID  
WHERE (d.LineType = 'LC') AND (o.A = 'SUBMITTED')  
group by d.OrderID) b   
on a.OrderID=b.OrderID  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The query in the SET clause needs to be correlated to the target table. SQL Server is not going to read between the lines to figure out what you mean. (Computers are generally born stupid.)

Presumably, this is what you want:

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')
                         AND o.OrderID = SO.OrderID
     ) 
FROM  @SubmittedOrders

But note that since I don't know your tables well, I had to take my chances.