delete xxx rows with teradata

Database
Junior Contributor

Re: delete xxx rows with teradata

Hi Carlos,

of course switching to ROW_NUMBER instead of RANK will prevent too many rows and a cursor will do exactly 10 rows.

But the main question is:

Why should anyone delete "10 rows"?

Hopefully not in a production database :-)

And if it's for testing purpose you probably don't care if it's exactly 10 rows.

I used RANDOM to test deletion of x percent:

DELETE FROM tab WHERE RANDOM(1,100) <= x

It's only approximately x percent, but if the table is large it's quite close.

The exact percentage really doesn't matter and the plan is much better than TOP/SAMPLE.

Dieter

Enthusiast

Re: delete xxx rows with teradata

Carlos - I agree with you in this case,however i didn mean to use the RANK function in the WHERE clause.

Enthusiast

Re: delete xxx rows with teradata

Importantly, we cant use dervied tables in DELETE.