Help- Very Complex SQL query

Question

Help- Very Complex SQL query

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 1

Jeff Moden (2/22/2010)
I guess I didn't understand that one... it sets all the rows to 50 instead of doing the requested data smear.

It works on my system :crying: but then I don't actually have SQL Server 2000 installed. It works fine on 2005 and 2008.

What do you get if you just run the SELECT in the derived table called RowsToUpdate?

Second point, make sure you have the test data correctly set up.

I reproduce the script from earlier (not my code!)...

if OBJECT_ID('tempdb..#tmp_Dates1') is not null

drop table #tmp_Dates1

create table #tmp_Dates1

(

value_date datetime,

value_date_minus_1 datetime,

notional numeric(18,6)

)

-- PLEASE NOTE THAT THE FIRST ROW notional will always be NULL in this table

INSERT INTO #tmp_Dates1 VALUES ('2008-11-05 00:00:00.000','2008-11-04 00:00:00.000',NULL)

INSERT INTO #tmp_Dates1 VALUES ('2008-11-13 00:00:00.000','2008-11-12 00:00:00.000',10)

INSERT INTO #tmp_Dates1 VALUES ('2008-11-26 00:00:00.000','2008-11-25 00:00:00.000',20)

INSERT INTO #tmp_Dates1 VALUES ('2008-11-30 00:00:00.000','2008-11-30 00:00:00.000',30)

INSERT INTO #tmp_Dates1 VALUES ('2008-12-12 00:00:00.000','2008-12-11 00:00:00.000',40)

INSERT INTO #tmp_Dates1 VALUES ('2008-12-19 00:00:00.000','2008-12-18 00:00:00.000',50)

INSERT INTO #tmp_Dates1 VALUES ('2008-12-31 00:00:00.000','2008-12-31 00:00:00.000',60)

INSERT INTO #tmp_Dates1 VALUES ('2009-01-07 00:00:00.000','2009-01-06 00:00:00.000',70)

INSERT INTO #tmp_Dates1 VALUES ('2009-01-21 00:00:00.000','2009-01-20 00:00:00.000',80)

INSERT INTO #tmp_Dates1 VALUES ('2009-01-22 00:00:00.000','2009-01-21 00:00:00.000',90)

INSERT INTO #tmp_Dates1 VALUES ('2009-01-31 00:00:00.000','2009-01-31 00:00:00.000',100)

if OBJECT_ID('tempdb..#tmp_Dates_Notional') is not null

drop table #tmp_Dates_Notional

create table #tmp_Dates_Notional

(

value_date_minus_1 datetime,

notional numeric(18,6)

)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-06 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-07 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-08 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-09 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-10 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-11 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-12 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-13 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-14 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-15 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-16 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-17 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-18 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-19 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-20 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-21 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-22 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-23 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-24 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-25 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-26 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-27 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-28 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-29 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-30 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-01 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-02 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-03 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-04 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-05 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-06 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-07 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-08 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-09 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-10 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-11 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-12 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-13 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-14 00:00:00.000',NULL)

INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-15 00:00:00.000',NULL)

UPDATE t1

SET t1.notional = t2.notional

from #tmp_Dates_Notional t1

INNER JOIN

#tmp_Dates1 t2

on

t2.value_date_minus_1 = t1.value_date_minus_1

Note the UPDATE forms part of the setup.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

WayneS (2/22/2010)
I must have missed the updated requirements... let me look at what those are and see what I can do.

The updated test data setup script is in my reply to Jeff, if you need it.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 3

Paul White (2/22/2010)
WayneS (2/22/2010)
I must have missed the updated requirements... let me look at what those are and see what I can do.
The updated test data setup script is in my reply to Jeff, if you need it.

Thanks for the updated test data setup script. Using that, this is the code that I came up with to use the quirky update.

-- build a clustered index in DESCENDING order on the table being updated

CREATE CLUSTERED INDEX [IX_value_date_minus_1] ON #tmp_Dates_Notional (value_date_minus_1 DESC)

-- need a few variables to do the quirky work

declare @notional numeric(18,6),

@date1 datetime

-- get the max date that has a notional assigned to it

SELECT @date1 = MAX(value_date_minus_1)

FROM #tmp_Dates_Notional

WHERE notional IS NOT NULL

-- get the next notional above that date

-- this value is used as the starting point in case the first row being updated

-- (the last value_date_minus_1 value) has a NULL notional

SELECT @notional = MIN(notional)

FROM #tmp_Dates1

WHERE value_date_minus_1 > @date1

-- this form of the update statement needs to follow the rules as defined at:

-- http://www.sqlservercentral.com/articles/T-SQL/68467/

UPDATE #tmp_Dates_Notional

SET @date1 = value_date_minus_1, -- anchor column

@notional = notional = IsNull(notional, @notional)

FROM #tmp_Dates_Notional WITH (TABLOCKX) -- exclusive table lock: Not necessary for temp tables... but if you always use it, you won't forget it!

OPTION (MAXDOP 1) -- Prevent parallelism

-- show the results

select * from #tmp_Dates_Notional order by value_date_minus_1

Now, the results from this deviate ever-so-slightly from the expected results as specified in this post... specifically for value_date_minus_1 value 12/12/2008. In #tmp_Dates1, it looks like the last date for a notional of 40 is 12/11/2008, so it seems that the results from the above is indeed correct, and that the expected results are wrong for 12/12/2008. Would you please verify that this is correct?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 4

WayneS (2/22/2010)
Now, the results from this deviate ever-so-slightly from the expected results as specified in this post... specifically for value_date_minus_1 value 12/12/2008. In #tmp_Dates1, it looks like the last date for a notional of 40 is 12/11/2008, so it seems that the results from the above is indeed correct, and that the expected results are wrong for 12/12/2008. Would you please verify that this is correct?

Excellent job there Wayne. Classic Quirky Update.

The results match the ones from my effort, so unless the OP says anything different, it looks good to me 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 5

Paul White (2/22/2010)
Totally awesome job there Wayne.

Fixed that for you, Paul. 😀

... but seriously, Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 6

WayneS (2/23/2010)
Paul White (2/22/2010)
Totally awesome job there Wayne.
Fixed that for you, Paul. 😀
... but seriously, Thanks!

:laugh: funny :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 7

Paul White (2/23/2010)
WayneS (2/23/2010)
Paul White (2/22/2010)
Totally awesome job there Wayne.
Fixed that for you, Paul. 😀
... but seriously, Thanks!
:laugh: funny :laugh:

Payback... just couldn't resist. 😛

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

Paul White (2/22/2010)
Jeff Moden (2/22/2010)
I guess I didn't understand that one... it sets all the rows to 50 instead of doing the requested data smear.
It works on my system :crying: but then I don't actually have SQL Server 2000 installed. It works fine on 2005 and 2008.
What do you get if you just run the SELECT in the derived table called RowsToUpdate?
Second point, make sure you have the test data correctly set up.
I reproduce the script from earlier (not my code!)...
Note the UPDATE forms part of the setup.
Paul

I must have missed a piece of the setup previously. Your code works in 2k5 just fine, now. In 2k, though, it does this...

[font="Courier New"]

Server: Msg 4421, Level 16, State 1, Line 1

Derived table 'RowsToUpdate' is not updatable because a column of the derived table is derived or constant.

[/font]

--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)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

WayneS (2/22/2010)
Paul White (2/22/2010)
WayneS (2/22/2010)
I must have missed the updated requirements... let me look at what those are and see what I can do.
The updated test data setup script is in my reply to Jeff, if you need it.
Thanks for the updated test data setup script. Using that, this is the code that I came up with to use the quirky update.
-- build a clustered index in DESCENDING order on the table being updated
CREATE CLUSTERED INDEX [IX_value_date_minus_1] ON #tmp_Dates_Notional (value_date_minus_1 DESC)
-- need a few variables to do the quirky work
declare @notional numeric(18,6),
@date1 datetime
-- get the max date that has a notional assigned to it
SELECT @date1 = MAX(value_date_minus_1)
FROM #tmp_Dates_Notional
WHERE notional IS NOT NULL
-- get the next notional above that date
-- this value is used as the starting point in case the first row being updated
-- (the last value_date_minus_1 value) has a NULL notional
SELECT @notional = MIN(notional)
FROM #tmp_Dates1
WHERE value_date_minus_1 > @date1
-- this form of the update statement needs to follow the rules as defined at:
-- http://www.sqlservercentral.com/articles/T-SQL/68467/
UPDATE #tmp_Dates_Notional
SET @date1 = value_date_minus_1, -- anchor column
@notional = notional = IsNull(notional, @notional)
FROM #tmp_Dates_Notional WITH (TABLOCKX) -- exclusive table lock: Not necessary for temp tables... but if you always use it, you won't forget it!
OPTION (MAXDOP 1) -- Prevent parallelism
-- show the results
select * from #tmp_Dates_Notional order by value_date_minus_1
Now, the results from this deviate ever-so-slightly from the expected results as specified in this post... specifically for value_date_minus_1 value 12/12/2008. In #tmp_Dates1, it looks like the last date for a notional of 40 is 12/11/2008, so it seems that the results from the above is indeed correct, and that the expected results are wrong for 12/12/2008. Would you please verify that this is correct?

Not only is it a pleasure to see someone else use the quirky update instead of trying to install handrails on my hiney, but it's a real pleasure to see someone do it correctly and that includes the ORDER BY on the final SELECT. Add in the meaningful comments you included to the nicely formatted code you made and I'm flat out impressed. Thanks for the eye candy, Wayne.

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 10

Jeff Moden (2/23/2010)
I must have missed a piece of the setup previously. Your code works in 2k5 just fine, now. In 2k, though, it does this...
[font="Courier New"]Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'RowsToUpdate' is not updatable because a column of the derived table is derived or constant.[/font]

Bugger! 😀

That's a shame - though I did put (2000 compatible?) with the question mark in my original post because I don't have it installed to test it. I don't even own the software any more, otherwise I'd have another go. Oh well. I'll have to settle for the 2005 and 2008 solutions - though of course the quirky update is there for all versions 😉

If anyone out there with 2000 wants to refine my 2000 code so that it actually runs (always a good thing!), I'd be delighted!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

Jeff Moden (2/23/2010)
Paul White (2/22/2010)
Jeff Moden (2/22/2010)
I guess I didn't understand that one... it sets all the rows to 50 instead of doing the requested data smear.
It works on my system :crying: but then I don't actually have SQL Server 2000 installed. It works fine on 2005 and 2008.
What do you get if you just run the SELECT in the derived table called RowsToUpdate?
Second point, make sure you have the test data correctly set up.
I reproduce the script from earlier (not my code!)...
Note the UPDATE forms part of the setup.
Paul
I must have missed a piece of the setup previously. Your code works in 2k5 just fine, now. In 2k, though, it does this...
[font="Courier New"]
Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'RowsToUpdate' is not updatable because a column of the derived table is derived or constant.
[/font]

Ah... I see... it's a bit of a classic omission that they apparently allow in SQL Server 2k5. Here's your code, Paul, with a bit of a correction...

UPDATE #tmp_Dates_Notional

SET notional = ISNULL(new_value, 50)

FROM #tmp_Dates_Notional tdn

JOIN (

SELECT T1.value_date_minus_1,

T1.notional,

new_value =

(

SELECT MIN(notional)

FROM #tmp_Dates_Notional T2

WHERE T2.value_date_minus_1 >= T1.value_date_minus_1

AND T2.notional IS NOT NULL

)

FROM #tmp_Dates_Notional T1

WHERE T1.notional IS NULL

) AS RowsToUpdate

ON tdn.value_date_minus_1 = RowsToUpdate.value_date_minus_1

More about the "classic omission"... Here's one of the rules that I never (and, yep... I know I used "never") deviate from... if there is a join involved in an UPDATE, no matter where that join may occur in the UPDATE, the object of the UPDATE absolutely must be included in the main FROM clause of the UPDATE.

I learned that the hardway... My DBA came to me one day with a very, very simple UPDATE that one of the Developers had written. It contained a query that looked something like this....

UPDATE TableA

SET SomeColumn = TableB.SomeOtherColumn

FROM TableB

WHERE TableA.SomeID = TableB.SomeID

Except for the obvious incursion on the sensibilities of ANSI JOIN zealots, it looks just fine at first glance to most people. The two tables were well and properly indexed and it was supposed to update only 20,000 rows. That should have only taken seconds. Instead, it slammed 4 CPU's into the wall for twenty minutes.

The DBA had added "dbo" before bringing me the original code. No joy there. To make a much longer story shorter, they tried just about everything with indexes, etc. No joy. They moved the code from the 8 CPU production box to the 4 CPU QA box for additional testing and WHAM! The code ran in just over a second just like it was supposed to.

The problem turned out to be a very strange one... on the production server, the actual execution plan showed lines that were only 1 row thick and profiler showed that the code was recompiling for every row it updated. It turned out the indexes were just right and just enough parallelism was occuring where the optimizer totally lost it's mind. When the code was moved to the QA box (which had identical tables and rows) less parallelism occurred and it ran just fine and without all the recompiles.

Most folks would have added the MAXDOP option to the code and been done with it. Oddly enough, I was giving a class on the syntax for UPDATE and noticed that I had never seen an example of UPDATE in Books Online where the target table wasn't included in the FROM clause when a join was present. I changed the code to something similar to the following and it ran just fine on the production box.

UPDATE TableA

SET SomeColumn = TableB.SomeOtherColumn

FROM TableA, TableB

WHERE TableA.SomeID = TableB.SomeID

When we took TableA out of the FROM clause, it went back to being a server killer. Put the table back in the FROM clause, and it ran like a champ. We went back and forth several times just to be sure that's what the problem was.

Obviously, that's still crap code because it violates all sorts of best practices but it did prove that the object of the UPDATE must be included in the FROM clause when a JOIN is present.

For those interested, here's the proper way to write the code following "my" best practices...

UPDATE dbo.TableA

SET SomeColumn = b.SomeOtherColumn

FROM dbo.TableA a

INNER JOIN dbo.TableB b

ON a.SomeID = b.SomeID

As a sidebar, I've never been able to reproduce the problem. However, I have had several people complain about slow UPDATE's on this forum over the years. The ones that were missing the object of the UPDATE in the FROM clause when a JOIN was present were ALL fixed by adding the target table to the FROM clause with a proper join to it. And, like I said, doing otherwise is not a form that can be found in any of the examples in Books Online.

--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)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

For those interested, here's the proper way to write the code following "my" best practices...
UPDATE dbo.TableA
SET SomeColumn = b.SomeOtherColumn
FROM dbo.TableA a
INNER JOIN dbo.TableB b
ON a.SomeID = b.SomeID

Hi Jeff, I would normally do this slightly differently:

UPDATE a

SET a.SomeColumn = b.SomeOtherColumn

FROM dbo.TableA a

INNER JOIN dbo.TableB b

ON a.SomeID = b.SomeID

(the only difference being that I tend to use the alias instead of the table name in the UPDATE section)

Is this going to behave the same as yours? and do you know of any conditions under which it is better or worse to do it this way or "your" way?

Thanks

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Paul White SSC Guru Points: 150467 More actions · Answer 13

mister.magoo (2/23/2010)
Hi Jeff, I would normally do this slightly differently:
UPDATE a
SET a.SomeColumn = b.SomeOtherColumn
FROM dbo.TableA a
INNER JOIN dbo.TableB b
ON a.SomeID = b.SomeID
(the only difference being that I tend to use the alias instead of the table name in the UPDATE section)

Me too, but with the slight difference that I omit the alias from the left hand side of the SET:

UPDATE A

SET SomeColumn = B.SomeOtherColumn

FROM dbo.TableA A

JOIN dbo.TableB B

ON (A.SomeID = B.SomeID);

In fact I "never" deviate from that. 🙂

@jeff:

The reason I didn't do it here is because I tried to convert the 2005 updatable CTE as faithfully and obviously as possible. So, the CTE just became a derived table directly - I gave it no more thought once I saw it compile and run successfully in derived-table format on 2005 and 2008.

Not having SQL 2000 to test it, I didn't. Had I tested it, I sure would have given it some thought and posted with an explicit join, or something equally correct. The corrected 2000 version (many thanks for that by the way!) is less efficient since an extra join occurs in the query plan, but I have a feeling that was a limitation in 2000. No doubt someone will correct me on that in due course...!

That said, thanks for the great post there - you're absolutely right about being careful with UPDATE like that. It is many years since I ran into the problem you describe, and like Mr Magoo there, I have "always" written my UPDATEs not to fail like that since. The downside is that I simply cannot remember what the problem was either!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 14

Aha! I remember now...I had to change MIN to the equivalent TOP (1)...ORDER BY construction in order to make the CTE updatable.

For some daft reason (clarity?) I changed it back to MIN in the 2000-compatible attempt.

I am fairly confident this will work in 2000:

UPDATE RowsToUpdate

SET notional = ISNULL(new_value, 50)

FROM (

SELECT T1.notional,

new_value =

(

SELECT TOP 1 notional

FROM #tmp_Dates_Notional T2

WHERE T2.value_date_minus_1 >= T1.value_date_minus_1

AND T2.notional IS NOT NULL

ORDER BY notional ASC

)

FROM #tmp_Dates_Notional T1

WHERE T1.notional IS NULL

) AS RowsToUpdate;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 15

mister.magoo (2/23/2010)
For those interested, here's the proper way to write the code following "my" best practices...
UPDATE dbo.TableA
SET SomeColumn = b.SomeOtherColumn
FROM dbo.TableA a
INNER JOIN dbo.TableB b
ON a.SomeID = b.SomeID
Hi Jeff, I would normally do this slightly differently:
UPDATE a
SET a.SomeColumn = b.SomeOtherColumn
FROM dbo.TableA a
INNER JOIN dbo.TableB b
ON a.SomeID = b.SomeID
(the only difference being that I tend to use the alias instead of the table name in the UPDATE section)
Is this going to behave the same as yours? and do you know of any conditions under which it is better or worse to do it this way or "your" way?
Thanks

The only reason why I don't usually (there are exceptions when a self join is present) use an alias as the target of the UPDATE is because it makes it simpler to find out what may be using a table for what in a search in sysComments and its equivalent in the later version of SQL Server. Other than that, the only difference is less typing... the performance, execution plan, and operation are identical.

As a sidebar and oddly enough, using aliases in such a fashion has worked correctly for a lot longer than it's been documented in Books Online.

--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)