Archives of the TeradataForum
Message Posted: Fri, 11 Mar 2005 @ 22:39:40 GMT
Thanks to all for your replies regarding batch deletion. I have a follow up question..
Is there any way to declare variables and perform adhoc queries that involve loops iterations etc,. from the Teradata SQL Assistant? For example to delete rows from a table in batches, I would like to do this (delete 20% of rows at a time) :
declare modctr integer ; set modctr = 0; WHILE modctr < 5 DO begin transaction ; delete from Location where Location_ID mod 5 = :modctr ; end transaction; SET modctr = modctr +1 ; END WHILE;
This does not work since the Declare is not allowed.. The SQL can be converted as a procedure or a macro, but I do not have the permissions in prd.. In this specific case I can make 5 sql statements, but what if the iterations are much larger? Any Teradata client tool allows adhoc SQL with teradata procedural extensions?
On a different note, is transactional control required (begin & commit tran) for the above delete statement or every delete batch committed automatically?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|