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 to modify the above update sql to use sql table in place of hardcoded location codes.
Here are the 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?