title: TSQL Update Statement MS SQL Server 2008 R2
body: <p>I have a table to update. The value to update is an FK to a PK ID on a data table.</p>
  
  <p>The data table has date ranges and the table being updated has Date of Birth fields (Month, Day, Year). My Update statement could loop through all the records RBAR (row by agonizing row) But I was hoping to use a more set based solution. I've tried using the case statement and table joins in the from clause on the update statement but something about this problem is eluding me on how to approach it.  Here are the the table schemas and my attempt at an update statement</p>
  
  <pre><code>Table 1 Person:
  TABLE [dbo].[TFI_PERSON](
      [PERSON_ID] [int] IDENTITY(3500,1) NOT NULL,
      [HOROSCOPE_SIGN_ID] [int] NULL,
      [DOB_DAY] [int] NOT NULL,
      [DOB_MONTH] [int] NOT NULL,
      [DOB_YEAR] [int] NOT NULL,
  </code></pre>
  
  <p>Table 2 Horoscope</p>
  
  <pre><code>TABLE [dbo].[TFI_HOROSCOPE_SIGN](
      [HOROSCOPE_SIGN_ID] [int] IDENTITY(1,1) NOT NULL,
      [HOROSCOPE_SIGN] [nvarchar](100) NOT NULL,
      [HOROSCOPE_BEGIN_DATE] [datetime] NOT NULL,
      [HOROSCOPE_END_DATE] [datetime] NOT NULL,
  </code></pre>
  
  <p>Attempt(s) 1 &amp; 2</p>
  
  <pre><code>UPDATE P
  SET P.HOROSCOPE_SIGN_ID = HS.[HOROSCOPE_SIGN_ID] 
                  FROM dbo.TFI_PERSON AS P JOIN [dbo].[TFI_HOROSCOPE_SIGN] AS HS
                      ON P.[HOROSCOPE_SIGN_ID] = HS.[HOROSCOPE_SIGN_ID] 
  WHERE 
   CAST(DOB_YEAR AS NVARCHAR)+ '-' + CAST(DOB_MONTH AS NVARCHAR) + '-' + CAST(DOB_DAY AS NVARCHAR) BETWEEN HS.[HOROSCOPE_BEGIN_DATE] AND HS.[HOROSCOPE_END_DATE] 
  
  UPDATE dbo.TFI_PERSON
  SET HOROSCOPE_SIGN_ID = (SELECT HOROSCOPE_SIGN_ID 
      FROM dbo.TFI_HOROSCOPE_SIGN 
      WHERE CAST(CAST(DOB_YEAR AS NVARCHAR)+ '/' + CAST(DOB_MONTH AS NVARCHAR) + '/' + CAST(DOB_DAY AS NVARCHAR) AS DATETIME) BETWEEN [HOROSCOPE_BEGIN_DATE] AND [HOROSCOPE_END_DATE] )
  </code></pre>
  
  <p>Thanks for the assist.</p>