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

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
Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
How would I fix it?
0
ste5anSenior DeveloperCommented:
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?
0
Scott PletcherSenior DBACommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.