There exists nothing like this teradata (delete xxx rows).

You need to find a where condition which specifies the rows you want to delete.

delete from tableA

where col1 = xx

and col2 = yy;

Hi

You can also use RANK function to fetch the first 10 records from the table for deletion

Use SAMPLE to delete random rows:

delete from tab
where PK_col in
 (select * from (select PK_col from tab sample 10) dt)

The same should also be possible using TOP syntax.

Dieter

Top N is not allowed in derived queries.

So the only way to emulate the Sybase syntax would be dieters example.

Hi Ulrich,

SAMPLE and TOP are both not allowed in subqueries ;-)

I assume it's because it might be a Correlated Subquery. But there's no Correlated Derived Table, that's why i had to use this  strange syntax.

Dieter

Hi Dieter,

juup, I missed this bit in your query :-). And Top is working there as well.

Thanks Ulrich

Just a few thoughts:

Stalin:

Not at all. RANK can give you more rows than expected:

SELECT * FROM MY_DB.PRUEBA01 ORDER BY 1;

*** Query completed. 9 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_N C_TXT
----------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO3
5 CINCO5
5 CINCO4
5 CINCO2
5 CINCO1

BTEQ -- Enter your SQL request or BTEQ command:

your query will retrieve more rows than the specified 'TOP' (3 in this example):

SELECT * FROM MY_DB.PRUEBA01 QUALIFY RANK() OVER (ORDER BY ID_N DESC) <=3;

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_N C_TXT
----------- -------------------------
5 CINCO3
5 CINCO4
5 CINCO2
5 CINCO1
5 CINCO5

Besides that, Ordered Analytical Functions not allowed in WHERE Clause. (error 5479)

Dieter/Ulrich:

OK with your solution, but it only works with tables with a Primary Key. I know this is something that every table SHOULD have, but unfortunately -at least in my experience- this does not always happen :(.

I think the 'universal' solution will be a cursor loop with DELETE WHERE CURRENT OF and a counter to limit the 'TOP N' rows.

Cheers.

Carlos.

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

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

Importantly, we cant use dervied tables in DELETE.