Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2004 @ 12:57:22 GMT

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

OK - when you start think to the end - SP below works without alter table but still deletes only based on given ROW info...

I was trying to define a cursor with order by (as this would enable to delete without row info) but this seems not to work. Need to check this in the manual.

Again - I would expect poor to horrible performance.

Personally I thing -

1. Insert/select with group by into a new table in case of many duplicates

or

2. Insert/select with group by and having count(*) > 1 into TMP table.

3. delete from big table via join to tmp table

4. Insert/select from tmp to big table

in case of low number

should be best for this problem.

Ulrich

SQL:

     CREATE PROCEDURE del_a (in a integer)
     BEGIN
     DECLARE U_ID INTEGER;
     SET U_ID = 0;
     FOR for_loop AS cursor1 CURSOR FOR
     SELECT cast(a as varchar(1000)) as a
     FROM a
     where a = :a
     DO
     SET U_ID = U_ID + 1;
     IF U_ID > 1 THEN
     delete from a WHERE CURRENT OF cursor1;
     END IF;
     END FOR;
     END;

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