Archives of the TeradataForum

Message Posted: Thu, 10 Mar 2005 @ 16:42:47 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

Thanks.

	<Prev	Next>		<<First	<Prev	Next>	Last>>