question

EM-0355 avatar image
0 Votes"
EM-0355 asked MelissaMa-msft answered

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

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.

131722-2021-09-13-15-43-55.png

Thanks for your help.


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @EM-0355,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.