Zero out Parent row from a Merge Statement

Question

Zero out Parent row from a Merge Statement

LinksUp

SSCertifiable

Points: 6526
More actions
October 13, 2014 at 1:09 pm

#297331

I have merge statement that works reasonably well in most circumstances. However, in some instances I need the merge to zero out the parent, but it doesn't because the select statement in the merge returns 0 records.
Given these 2 tables and data:
declare @trvalue table
(
Parcel varchar(13)
,Acres decimal(11,2)
,Market int
,Taxable int
)
declare @trgreen table
(
Parcel varchar(13)
,Acres decimal(11,2)
,Market int
,Taxable int
,gr_status int
)
Insert @trvalue (Parcel, Acres, Market, Taxable)
Values ('00-0033-3490', '2.52', 39517, 607)
Insert @trgreen (Parcel, Acres, Market, Taxable, gr_status)
Values ('00-0033-3490', '3.00', 40000, 700, 1)
, ('00-0033-3491', '4.00', 50000, 800, 0)
, ('00-0033-3491', '5.00', 60000, 900, 0)
MERGE @TRValue V USING
(
SELECT G.Parcel, SUM(G.Acres) SumAcres, SUM(G.Market) SumMarket, SUM(G.Taxable) SumTaxable
FROM @TRGreen G
WHERE G.gr_Status = 0
GROUP BY G.Parcel
) GRB
ON (V.Parcel = GRB.Parcel)
WHEN MATCHED THEN
UPDATE SET
V.Acres = GRB.SumAcres
,V.Market = GRB.SumMarket
,V.Taxable = GRB.SumTaxable
WHEN NOT MATCHED THEN
INSERT (Parcel, Acres, Market, Taxable)
VALUES (GRB.Parcel, GRB.SumAcres, GRB.SumMarket, GRB.SumTaxable);
Select Parcel, Acres, Market, Taxable from @TRValue
What I would like to see is something like this:
Parcel Acres Market Taxable
00-0033-3490 0.00 0 0
00-0033-3491 9.00 110000 1700
Any ideas?
Thanks!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

Anybody have a thought on this?:-)

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I'm not sure if a "WHEN NOT MATCHED BY SOURCE" would be what you need. It gives the expected result, but I'm not sure if it's the logic needed.

MERGE @TRValue V USING

(

SELECT G.Parcel, SUM(G.Acres) SumAcres, SUM(G.Market) SumMarket, SUM(G.Taxable) SumTaxable

FROM @TRGreen G

WHERE G.gr_Status = 0

GROUP BY G.Parcel

) GRB

ON (V.Parcel = GRB.Parcel)

WHEN MATCHED THEN

UPDATE SET

V.Acres = GRB.SumAcres

,V.Market = GRB.SumMarket

,V.Taxable = GRB.SumTaxable

WHEN NOT MATCHED THEN

INSERT (Parcel, Acres, Market, Taxable)

VALUES (GRB.Parcel, GRB.SumAcres, GRB.SumMarket, GRB.SumTaxable)

WHEN NOT MATCHED BY SOURCE THEN

UPDATE SET

V.Acres = 0

,V.Market = 0

,V.Taxable = 0 ;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

LinksUp SSCertifiable Points: 6526 More actions · Answer 3

Luis Cazares (10/14/2014)
I'm not sure if a "WHEN NOT MATCHED BY SOURCE" would be what you need. It gives the expected result, but I'm not sure if it's the logic needed.

You are right. I needed to add some additional logic on the WHEN NOT MATCHED BY SOURCE to get the correct results for the entire table.

I just needed the extra pair of eyes to see what I was missing.

Thanks!

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/