Search This Blog

Tuesday, May 2, 2017

Update Table Joined with other on the basis of CASE statement

Please check the below example;

Declare @vTableA Table (CINAME varchar(100),  SerialNo varchar(100),   Status varchar(100),    UniquekeyCiname_serialno varchar(100))
Declare @vTableB Table (CINAME varchar(100),  SerialNo varchar(100),   Status varchar(100),    UniquekeyCiname_serialno varchar(100))

Insert into @vTableA
Select 'ASDAS','23SSD',NULL,'ASDAS23SSD'
Union All
Select 'WER34','',NULL,'WER34'
Union All
Select 'DFDFS','3239',NULL,'DFDFS3239'
Union All
Select 'ERTER',NULL,NULL,'ERTER'


Insert into @vTableB
Select 'ASDAS','23SSD','DEPLOYED','ASDAS23SSD'
Union All
Select 'WER34','5335','IN TRANSIT','WER345335'
Union All
Select 'DFDFS','3239','LOST','DFDFS3239'
Union All
Select 'ERTER','678W','DEAD','ERTER678W'


Select * from @vTableA
Select * from @vTableB

Update a
Set a.[Status] = b.[Status] 
From @vTableA a
Inner Join @vTableB b on Case when IsNull(a.SerialNo,'')<>'' then a.UniquekeyCiname_serialno else a.CINAME end = Case when IsNull(a.SerialNo,'')<> '' then b.UniquekeyCiname_serialno else b.CINAME end

Select * from @vTableA
Select * from @vTableB

No comments:

Post a Comment