T SQL Update statement that is set based

Question

Post reply

T SQL Update statement that is set based

Dale Cunningham

Old Hand

Points: 357
More actions
June 22, 2013 at 12:23 pm

#275468

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

does TFI_PERSON have a date of birth column stored as datetime format?

why this

[DOB_DAY] [int] NOT NULL,

[DOB_MONTH] [int] NOT NULL,

[DOB_YEAR] [int] NOT NULL,

rather than DOB (datetime) ?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Dale Cunningham Old Hand Points: 357 More actions · Answer 2

No sir.

That is why you see the string concatenation of the month day and year.

It would be easier but alas it wasn't built that way.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Trying to construct the date by concatenating three casts from int to nvarchar() is likely to be somewhat error-prone, and even if you get it right (eliminating spaces, ensuring month and day parts are two characters) it will perform very badly. It's easier to get right and might perform slightly better (not much - not having the the DOB as a datetime value will be a big performance drag) if you use DATEADD to construct the date -

DATEADD(day, DOB_DAY, DATEADD(month, DOB_MONTH, DATEADD(year, DOB_YEAR, 0)))

There's no need for any casting and it avoids the implicit conversion from nvarchar to datetime if you do it like that.

Tom

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 4

Add a calculated column to the table to create a persisted datetime column. It'll make life a lot easier.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)