Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T SQL Update statement that is set based Expand / Collapse
Author
Message
Posted Saturday, June 22, 2013 12:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 8:16 AM
Points: 11, Visits: 103
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.
Post #1466475
Posted Saturday, June 22, 2013 12:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,885, Visits: 18,484
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 !
__________________________________________________________________
Post #1466476
Posted Saturday, June 22, 2013 12:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 8:16 AM
Points: 11, Visits: 103
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.
Post #1466477
Posted Saturday, June 22, 2013 1:47 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
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
Post #1466484
Posted Sunday, June 23, 2013 9:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1466564
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse