Updating one table from another without a one-to-one relationship

Question

I am trying to fill out a date table that contains each date for the next 35 years with information about each day.

In my ERP system there is a table of accounting years (GLRULE), which determines the reporting periods for each specific year, since many companies do not work during calendar months.

The GLRule table contains one record for each period and looks like this:

fcbasis fcname fcstatus fdend fdstart flisadjust flisaudit fnnumber freval identity_column A FY 2000 C 1/28/2000 0:00:00 1/1/2000 0:00:00 FALSE FALSE 1 FALSE 37 A FY 2000 C 2/25/2000 0:00:00 1/29/2000 0:00:00 FALSE FALSE 2 FALSE 38 A FY 2000 C 3/31/2000 0:00:00 2/26/2000 0:00:00 FALSE FALSE 3 FALSE 39 A FY 2000 C 4/28/2000 0:00:00 4/1/2000 0:00:00 FALSE FALSE 4 FALSE 40 A FY 2000 C 5/26/2000 0:00:00 4/29/2000 0:00:00 FALSE FALSE 5 FALSE 41 A FY 2000 C 6/30/2000 0:00:00 5/27/2000 0:00:00 FALSE FALSE 6 FALSE 42 

In any case, I can update the date table field one at a time with a query like this:

 UPDATE redfridaydates.dbo.testdates SET [FISCAL_PERIOD] = (SELECT fnnumber FROM m2mdata01..glrule GLR where DATE >= GLR.FDSTART and DATE <= GLR.FDEND) 

Is there a better way to update multiple fields at a time? I am not sure how I can do this, since I have no participation.

Answer 1

It looks like the table you are trying to update has one entry for each date. The answer suggests that. If you can guarantee that your GL data source does not have matching dates:

 UPDATE T SET T.[FISCAL_PERIOD] = GLR.fnnumber, T.Foo = GLR.Bar, T.Bat = GLR.Baz --etc. FROM redfridaydates.dbo.testdates AS T INNER JOIN m2mdata01..glrule AS GLR ON T.[Date] BETWEEN GLR.FDSTART AND GLR.FDEND 

If you're interested, here is a proof of concept for some test data .

Answer 2

You can always set more fields in the update request:

 UPDATE TableName Set Field1 = (Select fnnumber From ....), Field2 = (some other query or value), etc. 

Answer 3

Maybe looping your date table would be the solution?

 declare @myDate SmallDateTime set @myDate = (Select MIN(DATE) FROM dbo.testdates) declare @myFiscal int WHILE @myDate is not null BEGIN --determine the fiscal period --output a zero for missing fiscal periods SET @myFiscal = isnull( ( SELECT fnnumber FROM m2mdata01..glrule GLR WHERE @myDate >= GLR.FDSTART AND @myDate <= GLR.FDEND ),0) --update the date table UPDATE redfridaydates.dbo.testdates SET fiscal_period = @fiscal WHERE date = @myDate --get the next date --will be null once the loop reaches the end of the table SET @myDate = (Select MIN(DATE) FROM dbo.testdates WHERE DATE > @myDAte) END