Force Order
-
Is there a way to force the ordering of data in an update statement.
I have a transaction table and I need a running balance for the transactions based on type (credit or debit). The data is not stored in a ID field order. and there a date field also which drives the order by.
so the order by would look something like 'date, type, tran_number, id'
Any suggestions.
-
cursor update could be an option.
-
I am trying to replace a fast_forward cursor. When we migrate clients we have to update abt 20k+ transactions and it took abt 35 minutes for abt 10k transactions. The total number of records in the table is ~600k.
Thanks
-
You could try a subquery, the BOL UPDATE example D. Use UPDATE with the TOP clause in a SELECT statement, should give a give you a clue, just ignore the TOP portion, but notice the ORDER BY.
I have NOT tried this for a running balance UPDATE.
Andy
-
Not sure if I understand exactly what you are doing but it sounds like you are doing a one-off update of an existing table, in which case you need something like:
Update trantable set RunningTotal = (SELECT sum(amount) FROM trantable t2 where t2.type = trantable.type and t2.date < trantable.date and t2.tran_number < trantable.tran_number)
This assumes that the tran_number goes up for each transaction added. You might need to add ID in the where clause if there is more than one transaction with the same tran_number.
Unfortunately I haven't got access to a transaction type table to check that I have got the syntax exactly right, but it should give you an guide.
This update won't be fast but it should be faster than a cursor based update.
Peter
-
Forgive my ignorance, but why do you store such redundant data like a running balance in your db at all?
The usual approach is either use something like this:
IF OBJECT_ID('lfdsum_t') IS NOT NULL
DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t (col1 int)
INSERT INTO lfdsum_t values (1);
INSERT INTO lfdsum_t values (2);
INSERT INTO lfdsum_t values (3);
INSERT INTO lfdsum_t values (4);
INSERT INTO lfdsum_t values (5);
SELECT
a.COL1
,(SELECT
Sum(b.col1)
FROM
lfdsum_t b
WHERE
b.col1 <= a.col1) lfd_Sum
FROM
lfdsum_t a
DROP TABLE lfdsum_t
COL1 lfd_Sum
----------- -----------
1 1
2 3
3 6
4 10
5 15
(5 row(s) affected)
which you can easily extent to use your date column, but might prove inefficient on larger tables or, and possibly better do this at your presentational layer at the client.
But I might be missing something here anyway.
Why did you put this thread in the "Notification Services" forum?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I tried that but it didnt work for me.
The data in the Select statement would pull in the order specified but would not update in the order required.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply