Using MAX in an UPDATE
-
What is the best way to accomplish this? This works, probably a faster way.
--largest transaction
SELECT
AcctNo, MAX(TransAmt) as LargestTicket
INTO #TempLargest
FROM _FinalTrans
GROUP BY AcctNo
UPDATE _FinalFlat SET
LargestTicket = B.LargestTicket
FROM _FinalFlat A
INNER JOIN #TempLargest B
ON A.AcctNo = B.AcctNo
DROP TABLE #TempLargest
-
UPDATE _FinalFlat SET
LargestTicket = B.LargestTicket
FROM _FinalFlat A
INNER JOIN
(
SELECT
AcctNo, MAX(TransAmt) as LargestTicket
FROM _FinalTrans
GROUP BY AcctNo
) B
ON A.AcctNo = B.AcctNo
-
Try this:
Update _FinalTrans set LargestTicket = B.LargestTicket
from ( Select Acct, MAX(TransAmt) as LargestTicket
FROM _FinalTrans
GROUP BY AcctNo ) B where _FinalTrans.Acct = B.Acct
-
Ohhh no, no, no... we ran into that little jem... "death by SQL"... pegged 4 cpus for two hours to do a lousy 2 minute update... THAT was repeatable with that particular piece of code. When we fixed it, it only took 2 minutes as expected. You just gotta trust me on this one... if there is a join on the object of the update, the object of the update absolutely MUST be included in the FROM clause.
You can't make it happen predictably and they may have finally fixed it in 2k sp4 but even Books Online lists all joined updates with the object of the update in the FROM clause. Don't take a shortcut on this one...
--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)
Intro to Tally Tables and Functions -
As usual, Remi did it absolutely correct in the first reply above...
--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)
Intro to Tally Tables and Functions -
Ya made the mistake of the (implicit) cross join once... not gonna do it again
. -
I've done this before and haven't had problem. But its good to know I will avoid it in future.
Thanks
Sreejith
-
Sreejith,
Yep... we had a bunch of these... they all worked fine until, one day... That's what's so insidious about it. Works fine until you reach some unknown tipping point and then, WHAMMO! The implicit cross-join Remi talked about takes effect and the performance decreases by a factor of X2 where "X" is the number of rows in the table (never mind how many you want to update).
--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)
Intro to Tally Tables and Functions -
You bet, Remi... we actually put the requirement in our "SQL Coding Standards" document so the Developers are made keenly aware of the problem. Since we do code reviews on every piece of SQL, we don't have the problem anymore.
--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)
Intro to Tally Tables and Functions -
Are you guys hiring junior DBAs? I'd love ot come in and start a career (not that I don't like programming but sql server is just more of a calling for me than vb x).
-
Job Requirements:
- Must know T-SQL inside and out
- Must at least know how to spell "performance code"
- Must comply with the "SQL Standards Document" without exception
- Must like beer of all types
- Must like to shoot pool whilst imbibing
- Must tolerate really stupid requirements documents
- Must still be able to get the job done and protect the data despite the ID-TEN-T's responsible for requirement 6.
- Cheerfully work unnecessary unpaid overtime caused by same and those responsible for the ridiculous schedule.
- Must know why the following T-Shirt saying is true... "There are 10 types of people... those who know binary and those that don't."
--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)
Intro to Tally Tables and Functions - Must know T-SQL inside and out
-
1. Checked
2. CHECKED!!!!!!!!!
3. Check.
4. Sorry don't like beer
5. On my very best day I can beat the world champion. On my normal day I can beat 99.9% of the players in the country.
6. I can live with that
7. Checked.
8. Checked.
9. Still love that joke... expecially when you're one of the 0010 guys who gets it
.Where do I send my resume?
-
I started this thread, do I get a headhunters fee?
-
I can forward you all the beer I won't drink. But you have to pay the shipping charges
. -
Sure... I'll send you the shrunken head of a Developer that I didn't like
--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)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply