Tsql Select CASE

February 24, 2023

i need help in writing tsql select statement for my update:

Existing SQL update statement:

Update etl.dbo.SVRSummaryDataStage
    SET     Location = CASE WHEN Location IN ('131200','131400','131500','132400') THEN '131200'
    WHEN Location IN ('130600','131600','131700','131800') THEN '130600'
    WHEN Location IN ('130700','130800','130900','131100','131300','131900','132200') THEN '130700'
    WHEN Location IN ('130100','130200','130300') THEN '130110'
    WHEN Location IN ('130400','130500','132100') THEN '130120'
    END 
    WHERE  Location IN ('131200','131400','131500','132400','130600','131600','131700','131800','130700','130800','130900',
    '131100','131300','131900','132200','130100','130200','130300','130400','130500','132100');

I want modify the above update sql to use sql table with the hardcoded locations.
Here are my selects statements that i need to use in my update sql query:

SELECT  TOP 1 LEFT(CONCAT(CostCenter_ID  ,'000000'),6)
  FROM [Dashboard].[dbo].[CostCenter]
  WHERE BranchId IN ('1312','1314','1315','1324') AND Active=1
  --131200

  SELECT   TOP 1  LEFT(CONCAT(CostCenter_ID  ,'000000'),6)
  FROM [Dashboard].[dbo].[CostCenter]
  WHERE BranchId IN ('1306','1316','1317','1318') AND Active=1
  --130600

  SELECT  TOP 1  LEFT(CONCAT(CostCenter_ID  ,'000000'),6)
  FROM [Dashboard].[dbo].[CostCenter]
  WHERE BranchId IN ('1307','1308','1309','1311','1313','1319','1322') AND Active=1
  --130700

  SELECT  TOP 1  LEFT(CONCAT(CostCenter_ID  ,'000000'),6)
  FROM [Dashboard].[dbo].[CostCenter]
  WHERE BranchId IN ('1301','1302','1303') AND Active=1
  --467000

  SELECT  TOP 1  LEFT(CONCAT(CostCenter_ID  ,'000000'),6)
  FROM [Dashboard].[dbo].[CostCenter]
  WHERE BranchId IN ('1304','1305','1321') AND Active=1
  --130400

--where clause query
  SELECT  LEFT(CONCAT(BranchID  ,'000000'),6)  FROM  [Dashboard].[dbo].[CostCenter]
  WHERE BranchID IN (
  '1312','1314','1315','1324','1306','1316','1317','1318','1307','1308','1309',
        '1311','1313','1319','1322','1301','1302','1303','1304','1305','1321' ) AND Active=1

Any help?

all details provided above
i want modify the above update sql to use sql table with the hardcoded locations

>Solution :

Okay, Updated SQL statement to replace the hardcoded locations with the table would look like this:

UPDATE etl.dbo.SVRSummaryDataStage
SET Location =
    CASE 
        WHEN Location IN (SELECT LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1312', '1314', '1315', '1324') AND Active = 1) THEN (SELECT TOP 1 LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1312', '1314', '1315', '1324') AND Active = 1)
        WHEN Location IN (SELECT LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1306', '1316', '1317', '1318') AND Active = 1) THEN (SELECT TOP 1 LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1306', '1316', '1317', '1318') AND Active = 1)
        WHEN Location IN (SELECT LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1307', '1308', '1309', '1311', '1313', '1319', '1322') AND Active = 1) THEN (SELECT TOP 1 LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1307', '1308', '1309', '1311', '1313', '1319', '1322') AND Active = 1)
        WHEN Location IN (SELECT LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1301', '1302', '1303') AND Active = 1) THEN (SELECT TOP 1 LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1301', '1302', '1303') AND Active = 1)
        WHEN Location IN (SELECT LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1304', '1305', '1321') AND Active = 1) THEN (SELECT TOP 1 LEFT(CONCAT(CostCenter_ID, '000000'), 6) FROM [Dashboard].[dbo].[CostCenter] WHERE BranchId IN ('1304', '1305', '1321') AND Active = 1)
    END
WHERE Location IN (
    SELECT LEFT(CONCAT(BranchID, '000000'), 6) 
    FROM [Dashboard].[dbo].[CostCenter]
    WHERE BranchID IN ('1312', '1314', '1315', '1324', '1306', '1316', '1317', '1318', '1307', '1308', '1309', '1311', '1313', '1319', '1322', '1301', '1302', '1303', '1304', '1305', '1321') 
    AND Active = 1
);