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

ahmed salah 3,216 Reputation points
2020-12-16T08:05:25.957+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 111.8K Reputation points
    2020-12-16T10:40:06.467+00:00

    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
    
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-12-16T08:18:37.727+00:00

    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

    0 comments No comments

  2. ahmed salah 3,216 Reputation points
    2020-12-16T08:32:11.57+00:00

    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

    0 comments No comments

  3. EchoLiu-MSFT 14,571 Reputation points
    2020-12-16T08:47:09.583+00:00

    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

    0 comments No comments

  4. ahmed salah 3,216 Reputation points
    2020-12-16T09:32:37.96+00:00

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

    0 comments No comments