Search This Blog

Showing posts with label Case. Show all posts
Showing posts with label Case. Show all posts

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