Archives of the TeradataForum

Message Posted: Fri, 11 Mar 2005 @ 22:39:40 GMT

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

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?

thanks.

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