Complex Case Statement in SQL SERVER

Uma 426 Reputation points
2023-10-09T15:25:26.1366667+00:00

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

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,899 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,206 Reputation points
    2023-10-09T15:56:59.94+00:00

    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;
    
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2023-10-10T02:29:11.6533333+00:00

    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.

    0 comments No comments