Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'


MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'

Author
Message
bugg
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
My 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.

    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 <> 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]);




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.

thanks
Cowboy DBA
Cowboy DBA
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 2255
Have you seen why it is hanging? Is it being blocked by another process?
bugg
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
Cowboy DBA (6/21/2013)
Have you seen why it is hanging? Is it being blocked by another process?


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
bugg
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
bugg (6/21/2013)
Cowboy DBA (6/21/2013)
Have you seen why it is hanging? Is it being blocked by another process?


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


Actually update is working, just delete is hanging..
Cowboy DBA
Cowboy DBA
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 2255
When 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.
Cowboy DBA
Cowboy DBA
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 2255
You might be suffering from parallelism. If you run this



sp_who2


And see several rows of the same SPID then there might be a parallel query issue

Then try this:


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 <> 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) ;



And see if that helps.

Would still like to understand what you mean by "hanging" though.
bugg
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
Apologies 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

WITH k_target AS
(
SELECT *
FROM Kart
WHERE sessionid = @sessionid
)
MERGE k_target


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'.
Cowboy DBA
Cowboy DBA
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 2255
This help?

WHEN NOT MATCHED BY SOURCE AND
c_target.sessionid = @sessionid
AND c_target.offer_type = 'GWP'

THEN DELETE


bugg
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
Cowboy DBA (6/21/2013)
This help?

WHEN NOT MATCHED BY SOURCE AND
c_target.sessionid = @sessionid
AND c_target.offer_type = 'GWP'

THEN DELETE



Unfortunately the offer_type doesn't exist in the target table Sad
Cowboy DBA
Cowboy DBA
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 2255
Assuming I've understood what you're on about you might have to do your delete as:

DELETE FROM dbo.cart C
LEFT 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



As I said earlier, you need to look at your query plan to ascertain what is going on.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search