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

Question

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

Answer 1

This variation seems to work too:

update u
set 
    u.category = isnull(nullif(i.category, ''), u.category), -- or: u.category = iif(i.category <> '', i.category, u.category),
    u.NameGeneration = i.NameGeneration
from #inputdata i 
inner join #DataUpdated u on u.PartId=i.PartId

Answer 2

Hi @ahmed salah ,

Try:

      update d   
      set d.category=i.category ,   
          d.NameGeneration=i.NameGeneration  
      from #DataUpdated d  
      inner join #inputdata i   
      on i.partid=d.partid and i.category <>'' or i.category <>null  

Or:

  update d   
  set d.category=i.category ,   
      d.NameGeneration=i.NameGeneration  
  from #DataUpdated d  
  inner join #inputdata i   
  on i.partid=d.partid   
  where(i.category <>'' or i.category <>null)  

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

thank you for reply
result not what i need

i need to update updatedata by partid only but if i have value on inputdata per category then update table updatedata
if category on input data blank or null then also update by partid but category will not update because it is blank or null

Answer 4

The update conditions of the two fields are different, so they need to be updated separately，I overlooked this.
Please refer to：

  update d 
  set d.category=i.category 
  from #DataUpdated d
  inner join #inputdata i 
  on i.partid=d.partid 
  where(i.category <>'' or i.category <>null)

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

Output:

    PartId category NameGeneration
    2020 Plastic v5070
    2030 Alomental v5080
    2050 Iron2 v5090
    2090 Plaster2 v8000

Echo

Answer 5

thank you for reply
can i do that on one statement update without using two statement update