Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

MERGE Statement DELETE from TARGET when not in SOURCE 'hanging' Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 4:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 193, Visits: 684
Cowboy DBA (6/21/2013)
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.


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 :)
Post #1466425
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse