Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2004 @ 12:56:28 GMT

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

You ask if it is possible - and the answer is yes

BUT it might not be advisable - only thing I can think about might be in case you have billions of rows in a multiset table and only a hand full are duplicate and need to be removed...

The following works (need to be executed in ANSI mode and need V2R5!!!):

1. alter table and add a field which can used to keep an unique ID.

2. Create a stored procedure which is updating the unique_ID field for a given -PK.

3. Call the SP for all PK's where duplicates exists.

4. Delete where unique_id field > 1;

5. alter table an remove unique ID fields

This works technical BUT be careful!!!

Ulrich

Example SQL:

     create multiset table a
     ( a integer );
     commit;


     insert into a values (1);
     insert into a values (2);
     insert into a values (2);
     insert into a values (3);
     insert into a values (3);
     insert into a values (3);
     commit;


     select * from a;


     alter table a
     add unique_id integer;
     commit;


     select * from a;


     CREATE PROCEDURE upd_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;
     UPDATE a
     SET unique_id = :U_ID WHERE CURRENT OF cursor1;
     END FOR;
     END;
     COMMIT;


     call upd_a(2);

     call upd_a(3);


     select *
     from a
     order by 1,2;


     delete from a
     where unique_id > 1;


     select *
     from a
     order by 1,2;


     alter table a
     drop unique_ID;
     commit;


     select *
     from a
     order by 1;


     drop PROCEDURE upd_a;
     commit;

     drop table a;
     commit;

--
Ulrich Arndt

www.data2knowledge.de

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