Heh... you know me all to well, Brother Lynn! 😀 Although Mathew did a very good job of attaching sample data, the column names are quite a bit different than those posted and I also wanted to show a shorter way to post sample data. And, as you so very well put it, I was playing with my typical million row example and then saw you beat me to answering this. The only thing you forgot was the TABLOCKX to keep people out of the table while the update is occuring. 🙂

Anyway, here's the short, heavily commented version... do read the comments as they make a point about this method of updating...

--===== Conditional drop the temporary test table (just so we can rerun the test, if we want)

IF OBJECT_ID('TempDB..#TempStk','U') IS NOT NULL

DROP TABLE #TempStk

--===== Creat the temporary test table with a clustered PK on Seq

CREATE TABLE #TempStk

(

ID INT PRIMARY KEY CLUSTERED, --Can be an IDENTITY, as well

Code INT,

Name NVARCHAR(300),

Txn_Type NVARCHAR(50),

Date DATETIME,

Quantity FLOAT,

Balance FLOAT

--===== Populate the test table with data. Notice the data is pretty much out of order so

-- far as the clustered PK is concerned. If the ID can't be maintained in the correct

-- order, not to worry... put the clustered index on Code, Name, and Date. Notice I

-- said nothing about that having to be a PK. ;-)

INSERT INTO #TempStk

(ID, Code, Name, Txn_Type, Date, Quantity)

SELECT '13','8112','OTRIVIN CHILD 0.5% NDrops','Issued','May 5 2004 12:00AM','10' UNION ALL

SELECT '6','8110','AGIOLAX','Receipt','Jan 1 2004 12:00AM','50' UNION ALL

SELECT '1','8100','ACTIFED','Receipt','Jan 1 2004 12:00AM','100' UNION ALL

SELECT '3','8100','ACTIFED','Issued','Apr 1 2004 12:00AM','5' UNION ALL

SELECT '4','8100','ACTIFED','Issued','May 5 2004 12:00AM','5' UNION ALL

SELECT '10','8112','OTRIVIN CHILD 0.5% NDrops','Receipt','Jan 1 2004 12:00AM','200' UNION ALL

SELECT '5','8100','ACTIFED','Issued','May 10 2004 12:00AM','5' UNION ALL

SELECT '8','8110','AGIOLAX','Receipt','Mar 28 2004 12:00AM','50' UNION ALL

SELECT '9','8110','AGIOLAX','Issued','May 5 2004 12:00AM','25' UNION ALL

SELECT '11','8112','OTRIVIN CHILD 0.5% NDrops','Receipt','Mar 28 2004 12:00AM','50' UNION ALL

SELECT '7','8110','AGIOLAX','Issued','Feb 12 2004 12:00AM','10' UNION ALL

SELECT '12','8112','OTRIVIN CHILD 0.5% NDrops','Issued','Apr 1 2004 12:00AM','15' UNION ALL

SELECT '2','8100','ACTIFED','Issued','Mar 28 2004 12:00AM','5'

--===== Declare some obviously named variables

DECLARE @PrevID INT,

@PrevCode INT,

@PrevName NVARCHAR(300),

@PrevBal FLOAT

--===== Do the "quirky" update using a very high speed pseudo-cursor,

-- This is very similar to what you would do in a language like "C" except the

-- "Read a row/Write a row" is built into the update.

UPDATE #TempStk

SET @PrevBal = Balance = CASE

WHEN Code = @PrevCode

AND Name = @PrevName

THEN @PrevBal + (Quantity * CASE WHEN Txn_Type = 'Issued' THEN -1 ELSE 1 END)

ELSE Quantity * CASE WHEN Txn_Type = 'Issued' THEN -1 ELSE 1 END

END,

@PrevCode = Code,

@PrevName = Name,

@PrevID = ID --Just an "anchor", but gotta have it to guarantee things.

FROM #TempStk WITH (INDEX(0), TABLOCKX)

--===== Display the results

SELECT *

FROM #TempStk

ORDER BY ID

Now, just in case anyone gets any smart ideas about using the clustered index in SELECTs to create an "order" without an ORDER BY... [font="Arial Black"]don't bloody well do it[/font]. Even though it looks like it works, there are times when it doesn't and I'll have the proof in the article I'm rewritting. For now, just take my word for it... the "orderless" ordering ONLY works in the "quirky" UPDATE and ONLY when it's properly formed with all the goodies like the "anchor" and forcing the clustered index scan.

The TABLOCKX is to keep people from pulling off an update or delete of the data we're trying to make the running total for. You might think that's a bad thing, but consider this... this method will update a million rows in less than 7 seconds and because it's gonna do the whole table, guess what it's eventually gonna do during those 7 seconds... you guessed it... it's going to lock the whole table, so just get it out of the way and do it up front.

I've gotta say it one more time... if you want a correct order out of a SELECT, you MUST use an ORDER BY. But, for the "quirky" UPDATE we did, you can quite literally "bank" on it. 😉

If anyone wants to see the million row update to make a running total, you'll just have to wait until I can publish the rewritten article. :hehe: