The flow of an UPDATE STATEMENT with a FROM CLAUSE

Question

Post reply

The flow of an UPDATE STATEMENT with a FROM CLAUSE

Viewing 6 posts - 16 through 20 (of 20 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

John

I reckon this...

[font="Courier New"] UPDATE [A] SET

[LAST_PAYMENT_DATE] = [P].[LAST_PAYMENT_DATE]

,[NEXT_PAYMENT_DATE] = .[NEXT_PAYMENT_DATE]

,[CUR_PAYMENT] = [P].[CUR_PAYMENT]

,[PMT_FREQ] = .[AVG_PMT_FREQ]

FROM [dbo].[Amortization] [A]

INNER JOIN [##PS] [P]

ON [A].[ID_NUMBER] = [P].[ID_NUMBER]

INNER JOIN [##PS]

ON [A].[ID_NUMBER] = .[ID_NUMBER]

WHERE [A].[TBL_SOURCEID] <> '1'

AND [P].[RECCNT] = 1

AND .[RECCNT] = 2

[/font]

...is functionally equivalent. The derived table doesn't seem to be required, nor the index hints.

The UPDATE [A rather than UPDATE TableName can under some circumstances make a dramatic improvement.

If you wish to use this, the you should check your SELECT and mine and ensure that the results are identical before running the UPDATE.

Cheers

ChrisM

Is it just me or are the edit windows getting wider and wider today?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Chris Morris (11/18/2008)
Is it just me or are the edit windows getting wider and wider today?

Heh, yeah, John is a bit tab happy. I usually use the prettifier to remove all the extra extra tabs so that I can read it :hehe:.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 3

I ran both mine and your updated statement...

How do interpret the Execution plan??? or should I post this as new question?

mine a

mine b

urs a

urs b

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi John

Compare the SELECT component of each statement.

Row count

Values if you feel it is necessary

Time.

This is what I mean by the SELECT component:

[font="Courier New"]SELECT [P].[LAST_PAYMENT_DATE]

,.[NEXT_PAYMENT_DATE]

,[P].[CUR_PAYMENT]

,.[AVG_PMT_FREQ]

FROM [dbo].[Amortization] [A]

INNER JOIN [##PS] [P]

ON [A].[ID_NUMBER] = [P].[ID_NUMBER]

INNER JOIN [##PS]

ON [A].[ID_NUMBER] = .[ID_NUMBER]

WHERE [A].[TBL_SOURCEID] <> '1'

AND [P].[RECCNT] = 1

AND .[RECCNT] = 2

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ken McKelvey SSCoach Points: 18843 More actions · Answer 5

Ken McKelvey

SSCoach

Points: 18843

November 19, 2008 at 4:19 am

#900422

Opps - already answered

Ken McKelvey SSCoach Points: 18843 More actions · Answer 6

Already answer - something wrong with my connection today.