SQLTeam.com | Weblogs | Forums

Can I use only one statment for update without using three statment update separately?

I work on SQL server 2012 I need to using only one statement for update without using three statement separately

using case when or any other thing without using three update separated

can i do three statement below on one statement .

create Procedure [Parts].[LCStampInsertDataJob]
    
 AS
    
 begin 
    
    
    
 update s set s.AffectedProductID=P.PartId,ApprovedValidation=1 from  [Parts].[LCStamp] s    
 inner join Parts.nop_part p on P.PartNumber=s.AffectedProduct
 where s.Category=1581910 and s.AffectedProductID is null
    
 update s set s.AffectedProductID=f.PartFamilyID,ApprovedValidation=1 from  [Parts].[LCStamp] s    
 inner join Parts.nop_partsfamily f on f.FamilyName=s.AffectedProduct
 where s.Category=1581911 and s.AffectedProductID is null
    
 update s set s.AffectedProductID=cf.ID,ApprovedValidation=1 from  [Parts].[LCStamp] s    
  INNER JOIN dbo.Core_Masked cf on cf.MaskedText=s.AffectedProduct
 where s.Category=1581912 and s.AffectedProductID is null
    
    
 End

so the same column, in the same table could get updated 3 difference ways.
3rd one might always win and overwrite every single time.

which one of these join tables takes precedence?
nop_part
nop_partsfamily
Core_Masked

1- nop_part
2- nop_partsfamily
3- Core_Masked

first precedence
nop_part after that
nop_partsfamily after that
Core_Masked

are you a freelancer? or do you work for a company?

update s 
  set s.AffectedProductID=
  
       case 
			when P.PartNumber is not null then P.PartId
			when f.FamilyName is not null then f.PartFamilyID
			when cf.MaskedText is not null then cf.ID
			else null
	   end ,
      s.ApprovedValidation=1 
from  [Parts].[LCStamp] s    
 left join Parts.nop_part p on P.PartNumber=s.AffectedProduct
 left join Parts.nop_partsfamily f on f.FamilyName=s.AffectedProduct
 left JOIN dbo.Core_Masked cf on cf.MaskedText=s.AffectedProduct
 where s.Category in (1581910, 1581911, 1581912) and s.AffectedProductID is null
2 Likes