asked on

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

ste5an

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 Mauriello

ASKER

How would I fix it?

ste5an

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?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul Mauriello

ASKER

Thank you