SQL Server 2014 query- trying to mark records for deletion based on an aggregate total within the same table?

Paul Mauriello
Paul Mauriello used Ask the Experts™
on
I'm trying to mark records for deletion based on an aggregate total within the same table?       If an aggregate grouping with the tblVesselMessageBunkerConsumption  is zero then I want to mark those rows for deletion  but not the ones that total greater then zero. Where am I going wrong because no matter what I do they end up all being marked?

            UPDATE c
               SET c.IsActive = 0,
                     c.IsDelete = 1
            FROM
             tblVesselMessage a
             JOIN tblVesselMessageBunkerConsumption c ON a.VesselMessageID = c.VesselMessageID
            CROSS APPLY (SELECT VesselMessageID,
                               UsageUserDictionaryID,
                               EngineUserDictionaryID,
                               isSlr,
                               RowDisplayOrder,
                               Quantity=SUM(ISNULL(Quantity,0)) ,
                               IsActive,
                               IsDelete
                         FROM tblVesselMessageBunkerConsumption
                          WHERE VesselMessageID = c.VesselMessageID
                           GROUP BY  VesselMessageID,
                                           UsageUserDictionaryID,
                                           EngineUserDictionaryID,
                                           isSlr,
                                           RowDisplayOrder,
                                           Quantity,
                                           IsActive,
                                           IsDelete )  b
       
            WHERE
                   a.EVFAcctID = 2
                   AND b.isActive = 1
                   AND b.isDelete = 0
                   AND b.RowDisplayOrder IS NOT NULL
                   AND b.Quantity = 0
                   AND c.isActive = 1
                   AND c.isDelete = 0
                   AND c.RowDisplayOrder IS NOT NULL
                   AND c.VesselMessageID = 15196
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
It's the missing correlation to your sub-query in the CROSS APPLY. You're calculating the same SUM for all rows with the same VesselMessageID.
Paul MaurielloSoftware Programmer Developer Analyst Engineer

Author

Commented:
How would I fix it?
ste5anSenior Developer

Commented:
Correlate it.

Without a concise and complete example it's impossible to tell what the correlating columns could be.. a time stamp, a row number, a counter?
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Here's my best guess at it:


UPDATE c
               SET c.IsActive = 0,
                   c.IsDelete = 1
            FROM
             tblVesselMessage a
             JOIN tblVesselMessageBunkerConsumption c ON a.VesselMessageID = c.VesselMessageID
            INNER JOIN (SELECT VesselMessageID,
                               Quantity=SUM(ISNULL(Quantity,0))
                         FROM tblVesselMessageBunkerConsumption
                         WHERE
                             b.VesselMessageID = 15196 --to match the outer WHERE condition
                             AND b.isActive = 1
                             AND b.isDelete = 0
                             AND b.RowDisplayOrder IS NOT NULL                            
                         GROUP BY VesselMessageID
                        ) AS b ON b.VesselMessageID = c.VesselMessageID AND b.Quantity = 0
            WHERE
                   a.EVFAcctID = 2
                   AND c.isActive = 1
                   AND c.isDelete = 0
                   AND c.RowDisplayOrder IS NOT NULL
                   AND c.VesselMessageID = 15196
Paul MaurielloSoftware Programmer Developer Analyst Engineer

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial