All Forums Database
elmismo999 8 posts Joined 02/12
27 Feb 2012
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 

 

 

elmismo999 8 posts Joined 02/12
27 Feb 2012

Thank you !!

ulrich 816 posts Joined 09/09
27 Feb 2012

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;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Stefans 38 posts Joined 02/12
27 Feb 2012

Hi

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

Stalin

dnoeth 4628 posts Joined 11/04
27 Feb 2012

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

Dieter

ulrich 816 posts Joined 09/09
27 Feb 2012

Top N is not allowed in derived queries.

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
27 Feb 2012

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

Dieter

ulrich 816 posts Joined 09/09
27 Feb 2012

Hi Dieter,

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

Thanks Ulrich

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

elmismo999 8 posts Joined 02/12
28 Feb 2012

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 

 

 

 

 

CarlosAL 512 posts Joined 04/08
28 Feb 2012

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.

dnoeth 4628 posts Joined 11/04
28 Feb 2012

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

Dieter

Stefans 38 posts Joined 02/12
28 Feb 2012

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

Stalin

BJ220 5 posts Joined 01/12
14 Mar 2012

Importantly, we cant use dervied tables in DELETE.

Cheers,
BJ

You must sign in to leave a comment.