CODEWITHSUNDEEP
sql-servermergesql-update
RARascon
RARascon

Reputation: 129

SSMS: Using MERGE INTO to Update Column from Same Table

I'm attempting to update values in a FiscalYear column using data from the same table. I've tried an example found here and modified it for my purpose, and it provides values when I run the SELECT but just can't get it do the actual update to the table. Please let me know what I'm missing or provide other suggestions. Thanks!

MERGE INTO dbo.dim_Date as Dest
USING 
(
SELECT
   CASE WHEN t1.ID >= '20101001' and  t1.ID < '20111001' THEN 'FY2011'
     WHEN  t1.ID >= '20111001' and  t1.ID < '20121001'THEN 'FY2012'
     WHEN  t1.ID >= '20121001' and  t1.ID < '20131001'THEN 'FY2013'
     WHEN  t1.ID >= '20131001' and  t1.ID < '20141001'THEN 'FY2014'
     WHEN  t1.ID >= '20141001' and  t1.ID < '20151001'THEN 'FY2015'
     WHEN  t1.ID >= '20151001' and  t1.ID < '20161001'THEN 'FY2016'
     WHEN  t1.ID >= '20161001' and  t1.ID < '20171001'THEN 'FY2017'
     WHEN t1.ID >= '20171001' and  t1.ID < '20181001' THEN 'FY2018'
     WHEN  t1.ID >= '20181001' and  t1.ID < '20191001'THEN 'FY2019'
     WHEN  t1.ID >= '20191001' and  t1.ID < '20201001'THEN 'FY2020'
     WHEN  t1.ID >= '20201001' and  t1.ID < '20211001'THEN 'FY2021'
     WHEN  t1.ID >= '20211001' and  t1.ID < '20221001'THEN 'FY2022'
     WHEN  t1.ID >= '20221001' and  t1.ID < '20231001'THEN 'FY2023'
     WHEN  t1.ID >= '20231001' and  t1.ID < '20241001'THEN 'FY2024'
     WHEN t1.ID >= '20241001' and  t1.ID < '20251001' THEN 'FY2025'
     WHEN  t1.ID >= '20251001' and  t1.ID < '20261001'THEN 'FY2026'
     WHEN  t1.ID >= '20261001' and  t1.ID < '20271001'THEN 'FY2027'
     WHEN  t1.ID >= '20271001' and  t1.ID < '20281001'THEN 'FY2028'
     WHEN  t1.ID >= '20281001' and  t1.ID < '20291001'THEN 'FY2029'
     WHEN  t1.ID >= '20291001' and  t1.ID < '20301001'THEN 'FY2030'
     WHEN  t1.ID >= '20301001' and  t1.ID < '20311001'THEN 'FY2031'
     WHEN t1.ID >= '20311001' and  t1.ID < '20321001' THEN 'FY2032'
     WHEN  t1.ID >= '20321001' and  t1.ID < '20331001'THEN 'FY2033'
     WHEN  t1.ID >= '20331001' and  t1.ID < '20341001'THEN 'FY2034'
     WHEN  t1.ID >= '20341001' and  t1.ID < '20351001'THEN 'FY2035'
     WHEN  t1.ID >= '20351001' and  t1.ID < '20361001'THEN 'FY2036'
     WHEN  t1.ID >= '20361001' and  t1.ID < '20371001'THEN 'FY2037'
     WHEN  t1.ID >= '20371001' and  t1.ID < '20381001'THEN 'FY2038'
     WHEN t1.ID >= '20381001' and  t1.ID < '20391001' THEN 'FY2039'
     WHEN  t1.ID >= '20391001' and  t1.ID < '20401001'THEN 'FY2040'
     WHEN  t1.ID >= '20401001' and  t1.ID < '20411001'THEN 'FY2041'
     WHEN  t1.ID >= '20411001' and  t1.ID < '20421001'THEN 'FY2042'
     WHEN  t1.ID >= '20421001' and  t1.ID < '20431001'THEN 'FY2043'
     WHEN  t1.ID >= '20431001' and  t1.ID < '20441001'THEN 'FY2044'
     WHEN  t1.ID >= '20441001' and  t1.ID < '20451001'THEN 'FY2045'
     WHEN  t1.ID >= '20451001' and  t1.ID < '20461001'THEN 'FY2046'
     WHEN  t1.ID >= '20461001' and  t1.ID < '20471001'THEN 'FY2047'
     WHEN  t1.ID >= '20471001' and  t1.ID < '20481001'THEN 'FY2048'
     WHEN  t1.ID >= '20481001' and  t1.ID < '20491001'THEN 'FY2049'
     WHEN  t1.ID >= '20491001' and  t1.ID < '20501001'THEN 'FY2050'
   end as fiscalyear 
 FROM dbo.dim_Date t1
) as src

    on Dest.ID = src.ID
  WHEN MATCHED THEN
UPDATE 
SET FiscalYear =  src.fiscalyear
;

Upvotes: 0

Views: 169

Answers (1)

Anon
Anon

Reputation: 10908

UPDATE dbo.dim_Date
SET FiscalYear = 'FY'+STR(YEAR(DATEADD(mm,3,t1.ID)),4)

Upvotes: 3

Related Questions