SQL Server mismatch row count in SELECT and UPDATE query with same conditions

user_6344133 0
Viewed 754

I am trying to match the update and select query count with same condition. but row count is coming with big difference.

UPDATE Query: 

 UPDATE  MKP set MKP.Quantity =  MKP.Quantity + LQD.Quantity, ModifiedDate = GETDATE()
         FROM IM_MarketPlace MKP 
              INNER JOIN IM_ChannelListings CL ON MKP.ListingID = CL.ListingID 
              INNER JOIN @ListingQuantityData LQD ON LQD.ChanelListingID = RTRIM(LTRIM((CL.ChannelListingID)))  and LQD.SalesChannelID = CL.ChannelID
              Left outer join IM_ListingVariations LV on LV.ListingCode = RTRIM(LTRIM((LQD.VariationSKU))) and  MKP.ListingVariationID = LV.ID and CL.ListingID=LV.ListingID
         WHERE MKP.IsActive =1 and MKP.IsDeleted=0 and CL.IsActive =1 and CL.IsDeleted=0 
               AND (LQD.VariationSKU is null OR (LQD.VariationSKU = LV.ListingCode and lv.ID = MKP.ListingVariationID))

Select Query

select count(mkp.ListingID) FROM IM_MarketPlace MKP 
              INNER JOIN IM_ChannelListings CL ON MKP.ListingID = CL.ListingID 
              INNER JOIN @ListingQuantityData LQD ON LQD.ChanelListingID = RTRIM(LTRIM((CL.ChannelListingID)))  and LQD.SalesChannelID = CL.ChannelID
              Left outer join IM_ListingVariations LV on LV.ListingCode = RTRIM(LTRIM((LQD.VariationSKU))) and  MKP.ListingVariationID = LV.ID and CL.ListingID=LV.ListingID
         WHERE MKP.IsActive =1 and MKP.IsDeleted=0 and CL.IsActive =1 and CL.IsDeleted=0 
               AND (LQD.VariationSKU is null OR (LQD.VariationSKU = LV.ListingCode and lv.ID = MKP.ListingVariationID))

Please help me out for this.

and also please let me know how @@rowcount will work for update query.

sql-server-2017 sql-server-2012 sql-server-2016 sql-server sql
1 Answers

this will happen if there is a one to many relationship between at least two of the tables involved in the joins.

The SELECT will count all rows including those multiplied out by the join. The UPDATE will just count the unique rows in IM_MarketPlace affected by the UPDATE.

Where there is a one to many relationship it is not deterministic which of the "many" rows joining to a specific row in IM_MarketPlace are used as the source in the update for that row.

user_732260
Related