Archives of the TeradataForum
Message Posted: Thu, 10 Mar 2005 @ 16:42:47 GMT
Is there any constrct (or ANSI SQL Extension) in Teradata to limit the number of rows affected by a SQL operation?
We occasionally need to delete large amount of rows from a table, and currently it is done as a large single operation, like "delete from sales where saledate < somedate " . This often results in a long running transaction since the above SQL may qualify tens of millions of rows and if rolled back for any reason it is often days before it is complete. So we would like to perform it in a loop with committing after deleting a fixed number of rows (say 1 million). In MS SQL Server, this would be done with SET ROWCOUNT. A pseudocode would look like this:
Set RowCount 1000000 -- Any following SQL operations would affect only 1 Mil rows declare RowsAffected as integer RowsAffected = 1000000 -- Initiate so the loop will be successful first time While (RowsAffected = 1000000) -- When the rowsaffected is less the 1 Mil, all rows are deleted begin begin tran delete from table where condition RowsAffected = @@rowcount commit tran end
This way my deleted are committed every million rows, and would exit when all the rows are deleted (@@rowcount is equivalent to ACTIVITY_COUNT). I would much appreciate any help from the forum members on converting this construct to teradata, to be precise, locating equivalent of RowCount in teradata.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|