Update Table based on Sum()

Question

Update Table based on Sum()

riverswillbeer

SSC Enthusiast

Points: 194
More actions
November 18, 2008 at 10:17 am

#383416

Hey Gang ....
Im trying to update a field in a table with '320' IF the resortid is = HOL and the balance owed is over $1000. Any advice is much appreciated....here is my code so far.
UPDATE testsalescontract set testsalescontract.user1 = '320'
from TESTsalescontract
where TESTsalescontract.resortid ='HOL' and cast('1000' as money) < (select (sum(mdtrans.amount)) from mdtrans )
Ive tried different variations of CASE and this with no luck. Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

I think you need to a filter on the last table in the nested.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

riverswillbeer SSC Enthusiast Points: 194 More actions · Answer 2

Im kinda a newbe at this .... please explain ... thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

The question Christopher is asking is what is in the mdtrans table that is also in the TESTsalescontract table? For example, is there a ResortID column in the mdtrans table?

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

riverswillbeer SSC Enthusiast Points: 194 More actions · Answer 4

Ahhhh ...... Well testsalescontract.id = mdtrans.contractid

riverswillbeer SSC Enthusiast Points: 194 More actions · Answer 5

Jeff Moden (11/18/2008)
The question Christopher is asking is what is in the mdtrans table that is also in the TESTsalescontract table? For example, is there a ResortID column in the mdtrans table?

Ok ... I see where you were going with that and I got it to work. Thanks for setting me in the right direction !!! I appreciate it bigtime.

UPDATE SalesContract

SET User1 = '320'

WHERE ResortID = 'HOL'AND (SELECT SUM(Amount) FROM mdtrans where salescontract.id = mdtrans.contractid) > 1000

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Thanks for the feedback...

Just a bit of undocumented warning... we've had several problems in the past where using a correlated subquery in the WHERE clause of an update will occasionally peg all the CPU's on a server because it switches the code to a form of RBAR worse than a cursor (causes a recompile for every row updated). It doesn't always happen... in fact, it's quite rare for it to happen. But, when it does happen, a six second update can peg a 4 CPU box at 100% for two hours.

To avoid that problem, you must have the target of the UPDATE included in the FROM clause and use a standard join instead of correlation. I don't have your data to test with, but I believe it will accomplish the same thing without the chance of pegging CPU's....

UPDATE dbo.SalesContract

SET User1 = '320'

FROM dbo.SalesContract sc

INNER JOIN

(--==== Find all contracts having a sum greater than 1000)

SELECT ContractID

FROM dbo.mdTrans

GROUP BY ContractID

HAVING SUM(Amount) > 1000) qc

ON sc.ID = qc.ContractID

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

riverswillbeer SSC Enthusiast Points: 194 More actions · Answer 7

Wow ... that would suck !! I thank you for the correction. I obviously try to solve the issue using web resources and trial and error first( I have no formal SQL training) but if I go a couple days and cant figure out the problem ... I come here ... and have always received successful help. In fact .... I always walk away with a method or syntax that I never seen or imagined. Thanks for your help guy.... much appreciated!

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

Thanks Jeff,

Very useful info there 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

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

riverswillbeer (11/20/2008)
Wow ... that would suck !! I thank you for the correction. I obviously try to solve the issue using web resources and trial and error first( I have no formal SQL training) but if I go a couple days and cant figure out the problem ... I come here ... and have always received successful help. In fact .... I always walk away with a method or syntax that I never seen or imagined. Thanks for your help guy.... much appreciated!

You bet... thanks for the feedback. Gotta say I like your attitude! You'll do very well, heh, BECAUSE you've had no formal training. 🙂

If you get a minute, take a look at the link in my signature... using the methods to post in there will usually get you a lightning fast response to your future questions with the added benefit that folks will actually take the time to test their solutions for you when they don't have to think about setting up test data and test tables, etc, etc.

--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: 1004406 More actions · Answer 10

Christopher Stobbs (11/20/2008)
Thanks Jeff,
Very useful info there 🙂

You bet, Chris... thanks for the feedback.

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