SQLTeam.com | Weblogs | Forums

How to prevent update to category only by part id when it be blank or NULL on inputdata table?

I work on sql server 2012 I face issue I need to do update data found on table #DataUpdated based on part id exist on table #inputdata .

my issue on category on inputdata will update #dataupdated table when it have value only and not equal NULL
OR Blank .
so I need to update full row by parts id .

my issue i can't avoid update to data inputdata by partid where category on input data is null or blank
so how to do that

update d set d.category=i.category from #DataUpdated d
inner join #inputdata i on i.partid=d.partid

 create table #inputdata
 (
 PartId  int,
 category nvarchar(50),
 NameGeneration  nvarchar(50)
 )
 insert into #inputdata(PartId,category,NameGeneration)
 values
 (2020,NULL,'v5070'),
 (2030, '','v5080'),
 (2050,'Iron2','v5090'),
 (2090,'Plaster2','v8000')
    
 create table #DataUpdated
 (
 PartId  int,
 category nvarchar(50),
 NameGeneration  nvarchar(50)
 )
 insert into #DataUpdated(PartId,category,NameGeneration)
 values
 (2020,'Plastic','v5050'),
 (2030,'Alomental','v5050'),
 (2050,'Iron','v5050'),
 (2090,'Plaster','v9000')
    
    
    
 result expected

SELECT * FROM #DataUpdated
PartId category NameGeneration
2020 Plastic v5070
2030 Alomental v5080
2050 Iron2 v5090
2090 Plaster2 v8000

You can do the update couple of different ways. For instance,

UPDATE du SET
    du.category = COALESCE(i.category,du.category),
    du.NameGeneration = COALESCE(i.NameGeneration, du.NameGeneration)
FROM
    #DataUpdated AS du
    INNER JOIN #inputdata AS i ON
        i.PartId = du.PartId;