Complex Case Statement in SQL SERVER

Question

I have 2 tables

One called as BI and the other called as DP

I want to update my DP table column (Brand and Brand Description) with BI table column (BrandName And Description) respectively.

The Key column from BI table is ChainID which is numeric 1 and 3,

where as in DP it is Chaincode which is string.

Chainid 1 has to mapped with both Chaincode FSC and PRC

Chainid 3 has to mapped with only Chaincode EPRC

DDL:-

Create table BI (
CHAINID INT,
BrandName VARCHAR (100),
Description VARCHAR (100)
)
INSERT BI
SELECT 1, 'Sony', 'Bravia' UNION ALL
SELECT 3, 'Panasonic','TH-55MX'

Create table DP (
CHAINCODE VARCHAR (50),
Brand VARCHAR (100),
BrandDescription VARCHAR (100)
)
INSERT DP
SELECT 'FSC', 'SNY','BraV' UNION ALL
SELECT PRC, 'SNY','BRAV' UNION ALL
SELECT 'EPRC', 'PAN','INDI'

Expected Output (DP Table) After Update

'FSC', 'SONY','Bravia'

'PRC, 'SONY','Bravia'

'EPRC', 'Panasonic','TH-55MX'

Thanks a ton

Answer 1

Hi @Uma,

Please try the following solution based on a cross reference mapping table added via Table Value Constructor.

-- DDL and sample data population, start
DECLARE @bi TABLE (ChainID INT, BrandName VARCHAR(100), Description VARCHAR(100));
INSERT @bi VALUES
(1, 'Sony', 'Bravia'),
(3, 'Panasonic','TH-55MX');

DECLARE @DP TABLE (ChainCode VARCHAR (50), Brand VARCHAR (100), BrandDescription VARCHAR (100));
INSERT @DP VALUES
('FSC', 'SNY','BraV'),
('PRC', 'SNY','BRAV'),
('EPRC', 'PAN','INDI');
-- DDL and sample data population, end

WITH XRef AS
(
	SELECT *
	FROM (VALUES 
		(1, 'FSC'),
		(1, 'PRC'),
		(3, 'EPRC')
	) AS rs(ChainID, ChainCode)
)
UPDATE d
SET Brand  = b.BrandName
	, BrandDEscription = b.Description
FROM @DP AS d INNER JOIN XRef AS x ON d.ChainCode = x.ChainCode
	INNER JOIN @bi AS b ON x.ChainID = b.ChainID;

-- test
SELECT * FROM @DP;

Answer 2

Hi @Uma

Try this:

;WITH NewDP AS
(SELECT *,CASE WHEN CHAINCODE IN ('FSC','PRC') THEN 1 
               WHEN CHAINCODE = 'EPRC' THEN 3 END AS CHAIN FROM DP)
UPDATE N
SET N.Brand = B.BrandName
   ,N.BrandDEscription = B.Description
FROM BI B JOIN NewDP N ON B.CHAINID=N.CHAIN

If there is a lot of values in BI table, then it is suggested that you created another table to store values of ChainID and Chaincode.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.