delete xxx rows with teradata

Database
Enthusiast

delete xxx rows with teradata

Hi, I would like to delete for example 10 rows from a table.

In Sybase it would be something like 

set rowcount 10

delete from table 

12 REPLIES
Enthusiast

Re: delete xxx rows with teradata

Thank you !!

Senior Supporter

Re: delete xxx rows with teradata

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;

Enthusiast

Re: delete xxx rows with teradata

Hi

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

Junior Contributor

Re: delete xxx rows with teradata

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

Senior Supporter

Re: delete xxx rows with teradata

Top N is not allowed in derived queries.

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

Junior Contributor

Re: delete xxx rows with teradata

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

Senior Supporter

Re: delete xxx rows with teradata

Hi Dieter,

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

Thanks Ulrich

Enthusiast

Re: delete xxx rows with teradata

ok thank you.

The way I see is to do something like

1)

insert into TABLE_TEMP select top 10 * from MY_TABLE

2)

delete from MY_TABLE

3)

insert into MY_TABLE select * from TABLE_TEMP 


Junior Supporter

Re: delete xxx rows with teradata

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.