CODEWITHSUNDEEP
sqlsql-serversql-server-2008
addohm
addohm

Reputation: 2455

Correct Update with Count Syntax

I'm trying to update multiple keyed records with a single query, but I don't know the correct syntax.

Here's my attempt:

UPDATE hL
SET hL.[QUANTITY_COMPLETED] = (SELECT COUNT([SERIAL_NUMBER]) 
                               FROM [dbo].[ORDER_ITEMS] oO
                               INNER JOIN [dbo].[JOB_HISTORY] jH ON jH.[ORDER_NUMBER] = Oo.[ORDER_NUMBER]
                               INNER JOIN [dbo].[JOB_HISTORY_LINE] hL ON hL.[LINE_NUMBER] = oO.[LINE_NUMBER]
                               WHERE oO.[LINE_NUMBER] = hL.[LINE_NUMBER])
FROM [dbo].[JOB_HISTORY_LINE] hL

The problem: the above will stick the SUM of the items in ORDER_ITEMS in all the entries in JOB_HISTORY_LINE. This isn't correct.

JOB_HISTORY_LINE just contains information for each LINE_NUMBER associated to an ORDER_NUMBER. Each item in ORDER_ITEMS has an associated LINE_NUMBER and ORDER_NUMBER that can be referenced through JOB_HISTORY, which contains the ORDER_NUMBER.

The goal: each line in JOB_HISTORY_LINES should show the associated COUNT of SERIAL_NUMBERS from the ORDER_ITEMS table.

Please help me straighten this out so I can learn why I'm screwing this up :)

Upvotes: 1

Views: 42

Answers (2)

J a c k
J a c k

Reputation: 11

UPDATE [hL]
SET [hL].[QUANTITY_COMPLETED]=[oo].[Total]
FROM [dbo].[JOB_HISTORY_LINE] [hL]
LEFT JOIN
 (
 SELECT [oO].[LINE_NUMBER]
       ,Count([SERIAL_NUMBER]) AS [Total]
 FROM [dbo].[ORDER_ITEMS] AS [oO]
 INNER JOIN [dbo].[JOB_HISTORY] AS [jH]
       ON [jH].[ORDER_NUMBER]=[Oo].[ORDER_NUMBER]
 INNER JOIN [dbo].[JOB_HISTORY_LINE] AS [hL]
       ON [hL].[LINE_NUMBER]=[oO].[LINE_NUMBER]
 GROUP BY [oO].[LINE_NUMBER]
 ) AS [OO]
 ON [oO].[LINE_NUMBER]=[hL].[LINE_NUMBER];

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Perhaps what you are looking for is a correlated subquery:

UPDATE hL
    SET hL.[QUANTITY_COMPLETED] = (SELECT COUNT([SERIAL_NUMBER])
                                   FROM [dbo].[ORDER_ITEMS] oO INNER JOIN
                                        [dbo].[JOB_HISTORY] jH
                                        ON jH.[ORDER_NUMBER] = Oo.[ORDER_NUMBER]
                                   WHERE oO.[LINE_NUMBER] = hL.[LINE_NUMBER]
                                  )
    FROM [dbo].[JOB_HISTORY_LINE] hL;

Upvotes: 1

Related Questions