[T-SQL] T SQL Update statement that is set based

---

T SQL Update statement that is set based Posted: 22 Jun 2013 05:23 AM PDT I have a table to update. The value to update is an FK to a PK ID on a data table.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 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,Table 2 Horoscope 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,Attempt(s) 1 & 2 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] )Thanks for the assist.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610