SQLServerCentral.com / SQL Server 2008 / T-SQL (SS2K8) / MERGE Statement DELETE from TARGET when not in SOURCE 'hanging' / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 16:17:18 GMT20RE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspx[quote][b]Cowboy DBA (6/21/2013)[/b][hr]Assuming I've understood what you're on about you might have to do your delete as:[code]DELETE FROM dbo.cart CLEFT OUTER JOIN (SELECT @sessionid as sessionid ,ISNULL(o.free_subsku,0) as subsku ,o.free_sku as mainsku ,ko.cartrsn ,o.qty_free * ko.qty as qty ,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight] FROM offer o inner join @cart_offer ko on o.id = ko.offer_id inner join products p on o.free_sku = p.sku left join subproducts sp on o.free_subsku = sp.sku WHERE o.active = 1 and o.offer_type = 'GWP' ) D ON C.sessionid = D.sessionid and C.mainsku = D.mainsku and C.subsku = D.subsku WHERE D.sessionid IS NULL AND C.sessionid = @sessionid[/code]As I said earlier, you need to look at your query plan to ascertain what is going on.[/quote]Thanks Cowboy, I will give that try, however i did get it to work another way. The price is always a certain amount for 'GWP' so i check the price , similar to the check for GWP :)Fri, 21 Jun 2013 16:46:08 GMTbuggRE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspxAssuming I've understood what you're on about you might have to do your delete as:[code]DELETE FROM dbo.cart CLEFT OUTER JOIN (SELECT @sessionid as sessionid ,ISNULL(o.free_subsku,0) as subsku ,o.free_sku as mainsku ,ko.cartrsn ,o.qty_free * ko.qty as qty ,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight] FROM offer o inner join @cart_offer ko on o.id = ko.offer_id inner join products p on o.free_sku = p.sku left join subproducts sp on o.free_subsku = sp.sku WHERE o.active = 1 and o.offer_type = 'GWP' ) D ON C.sessionid = D.sessionid and C.mainsku = D.mainsku and C.subsku = D.subsku WHERE D.sessionid IS NULL AND C.sessionid = @sessionid[/code]As I said earlier, you need to look at your query plan to ascertain what is going on.Fri, 21 Jun 2013 08:30:44 GMTCowboy DBARE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspx[quote][b]Cowboy DBA (6/21/2013)[/b][hr]This help?[code]WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid AND c_target.offer_type = 'GWP' THEN DELETE[/code][/quote]Unfortunately the offer_type doesn't exist in the target table :(Fri, 21 Jun 2013 08:22:44 GMTbuggRE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspxThis help?[code]WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid AND c_target.offer_type = 'GWP' THEN DELETE[/code]Fri, 21 Jun 2013 07:53:26 GMTCowboy DBARE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspxApologies I probably didn't make my self clear, the query wasn't hanging but was taking an age to execute to the point i would stop the query.I think it was because it was trying to delete everything in my target table that didn't have a match to the source.After creating my target table using a CTE[code] WITH k_target AS ( SELECT * FROM Kart WHERE sessionid = @sessionid ) MERGE k_target[/code] it worked but not as expected. It deleted everything that wasn't in my source for that sessionid. However the other records for that sessionid I want to keep as they are not of offer_type 'GWP'.Fri, 21 Jun 2013 07:46:31 GMTbuggRE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspxYou might be suffering from parallelism. If you run this[code]sp_who2[/code]And see several rows of the same SPID then there might be a parallel query issueThen try this:[code]MERGE dbo.cart AS c_target USING (SELECT @sessionid as sessionid ,ISNULL(o.free_subsku,0) as subsku ,o.free_sku as mainsku ,ko.cartrsn ,o.qty_free * ko.qty as qty ,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight] FROM offer o inner join @cart_offer ko on o.id = ko.offer_id inner join products p on o.free_sku = p.sku left join subproducts sp on o.free_subsku = sp.sku WHERE o.active = 1 and o.offer_type = 'GWP' ) as c_source ON c_target.sessionid = c_source.sessionid and c_target.mainsku = c_source.mainsku and c_target.subsku = c_source.subsku --Issue here?!?! WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid THEN DELETE WHEN MATCHED AND c_target.qty &lt;&gt; c_source.qty THEN UPDATE SET c_target.qty = c_source.qty ,c_target.price = c_source.price ,c_target.[weight] = c_source.[weight] WHEN NOT MATCHED BY TARGET THEN INSERT (sessionid,subsku,mainsku,qty,price,[weight]) VALUES (@sessionid,c_source.subsku,c_source.mainsku,c_source.qty,c_source.price,c_source.[weight])OPTION (MAXDOP 1) ;[/code]And see if that helps.Would still like to understand what you mean by "hanging" though.Fri, 21 Jun 2013 07:28:13 GMTCowboy DBARE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspxWhen you say "hanging" what do you mean? I take it as there is something blocking which in turn implies that the update would not complete.Like I said, check any blocking SPIDs and also check the query plan of your statement.Fri, 21 Jun 2013 07:20:59 GMTCowboy DBARE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspx[quote][b]bugg (6/21/2013)[/b][hr][quote][b]Cowboy DBA (6/21/2013)[/b][hr]Have you seen why it is hanging? Is it being blocked by another process?[/quote]No its not getting blocked its on my dev SQL database.However I've just spotted that the update doesn't work either. Only the insert[/quote]Actually update is working, just delete is hanging..Fri, 21 Jun 2013 07:16:12 GMTbuggRE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspx[quote][b]Cowboy DBA (6/21/2013)[/b][hr]Have you seen why it is hanging? Is it being blocked by another process?[/quote]No its not getting blocked its on my dev SQL database.However I've just spotted that the update doesn't work either. Only the insertFri, 21 Jun 2013 07:12:04 GMTbuggRE: MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspxHave you seen why it is hanging? Is it being blocked by another process?Fri, 21 Jun 2013 07:04:59 GMTCowboy DBAMERGE Statement DELETE from TARGET when not in SOURCE 'hanging'http://www.sqlservercentral.com/Forums/Topic1466158-392-1.aspxMy code runs fine until I try Delete an entry that is in my target table but not in my source. The insert & update all work.[code] MERGE dbo.cart AS c_target USING (SELECT @sessionid as sessionid ,ISNULL(o.free_subsku,0) as subsku ,o.free_sku as mainsku ,ko.cartrsn ,o.qty_free * ko.qty as qty ,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight] FROM offer o inner join @cart_offer ko on o.id = ko.offer_id inner join products p on o.free_sku = p.sku left join subproducts sp on o.free_subsku = sp.sku WHERE o.active = 1 and o.offer_type = 'GWP' ) as c_source ON c_target.sessionid = c_source.sessionid and c_target.mainsku = c_source.mainsku and c_target.subsku = c_source.subsku --Issue here?!?! WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid THEN DELETE WHEN MATCHED AND c_target.qty &lt;&gt; c_source.qty THEN UPDATE SET c_target.qty = c_source.qty ,c_target.price = c_source.price ,c_target.[weight] = c_source.[weight] WHEN NOT MATCHED BY TARGET THEN INSERT (sessionid,subsku,mainsku,qty,price,[weight]) VALUES (@sessionid,c_source.subsku,c_source.mainsku,c_source.qty,c_source.price,c_source.[weight]); [/code]I know I should attach some test data, but can anyone see anything obviously wrong in my code?Also cart table has about 5mil rows in it.thanksFri, 21 Jun 2013 06:39:47 GMTbugg