CODEWITHSUNDEEP
sqlsql-server
Finder
Finder

Reputation: 1217

When Merge null columns are not merged just inserted

My table and it's data are below

CREATE TABLE Sales (Id int ITDENTITY(1,1) NOT NULL,stateid int, district int Sitecount int)

CREATE TABLE Sales1(stateid int, district int,
Sitecount Int)

insert into Sales values (1,2,12)
insert into Sales values (1,3,20)
insert into Sales values (1, NULL, 10) 
insert into Salesi values (1,2,10) 
insert into Salesi values (1,2. 100) 
insert into Select values (1,ULL, 18)

I have used the below query to merge

MERGE Sales AS T 
USING (Select stateid, district, Sitecount from Sales1 group by stateid,district) as S
 ON(S.stateid =T.stateld and S.district=T.district) 
 WHEN MATCHED
Then UPDATE SET
T.Sitecount=T.Sitecount+S.Sitecount 

WHEN NOT MATCHED BY TARGET THEN INSERT (stateid,district,Sitecount) VALUES(S stateid, S.district, S.5itecount);

Whenever I run the query, if the matched data all columns are not null then only data merged, Otherwise it is inserted as a new row.

If district data is null, need to add the sitecount based on the stateid.How to achieve it. Suggest me..

Upvotes: 2

Views: 84

Answers (2)

Raymondo
Raymondo

Reputation: 587

Try this:

MERGE Sales AS T 
USING (Select stateid, district, Sitecount from Sales1 group by stateid,district) as S
 ON(ISNULL(S.stateid, -1) = ISNULL(T.stateld, -1) and ISNULL(S.district, '')= ISNULL(T.district, '')) 
 WHEN MATCHED
Then UPDATE SET
T.Sitecount=T.Sitecount+S.Sitecount 

WHEN NOT MATCHED BY TARGET THEN INSERT (stateid,district,Sitecount) VALUES(S stateid, S.district, S.5itecount);

The following element was adjusted to isnull the comparison fields as a null value as you have observed are treated differently and incorrectly (for what you desire) execute the insert element:

 ON(ISNULL(S.stateid, -1) = ISNULL(T.stateld, -1) and ISNULL(S.district, '')= ISNULL(T.district, ''))

Upvotes: 2

sacse
sacse

Reputation: 3744

You can match on stateid using ISNULL in join like below:

MERGE Sales AS T 
USING (Select stateid, district, max(Sitecount) Sitecount from Sales1 group by stateid,district) as S
 ON(S.stateid =T.stateid and isnull(S.district,'')=isnull(T.district, '')) 
 WHEN MATCHED
Then UPDATE SET
T.Sitecount=T.Sitecount+S.Sitecount

WHEN NOT MATCHED BY TARGET THEN INSERT (stateid,district,Sitecount) VALUES(S.stateid, S.district, S.Sitecount);

select * from Sales

Also, please note that I have used max(Sitecount) to avoid the duplicates in join. Please change as per your requirement.

Please find the db<>fiddle here.

Upvotes: 2

Related Questions