Update/Select Statement w/ Limited Where Clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update/Select Statement w/ Limited Where Clause

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-01-06 : 13:07:56

Anyone have a better way to do this? My problem is that the WHERE clause limits the date whereas I need the SUM function to pull in all dates.

CREATE PROCEDURE [dbo].[UpdateMonthPoints]

@ParmPeriod smalldatetime

AS

update mac592XActions
set MonthPoints = MonthPoints - mac592.Balance from mac592 -
(select SUM(Points * Qty) from mac592XActions
where CODE = 'RE' AND ParticipantID = mac592XActions)
where mac592XActions.ParticipantID = mac592.ParticipantID AND mac592XActions.Period = @ParmPeriod
GO

Thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-06 : 15:28:53

Can you post some sample data and an expected result set?

derketo
Starting Member

28 Posts

Posted - 2005-01-06 : 17:11:44

BEFORE UPDATE:

mac592
ParticipantID,Balance
3000123,10000

mac592XActions
ParticipantID,Period,Points,Qty,MonthPoints,Code
3000123,12/1/2004,0,0,10200,ST
3000123,11/1/2004,100,3,0,RE

AFTER UPDATE:

mac592
ParticipantID,Balance
3000123,10000

mac592XActions
ParticipantID,Period,Points,Qty,MonthPoints,Code
3000123,12/1/2004,0,0,100,ST
3000123,11/1/2004,100,3,0,RE

I know it's screwy but in my defense I'm maintaining code that's not mine ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources