DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

General
Enthusiast

DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

Hi,

I am new to teradata can any one explain how to delete duplicates ?
My requirement is as follows

TABLE A
X Y Z
10 JOHN 100
10 JOHN 100
10 JOHN 100
20 SMITH 200
20 SMITH 200

MY OUTPUT :
10 JOHN 100
20 SMITH 200
I need only sql query using teradata please do the needful awaiting for ur response
7 REPLIES
Junior Contributor

Re: DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

Remove duplicates in SELECT? DISTINCT or GROUP BY

Actually delete duplicates from table?
- insert/select into a new SET table or
- insert/select into a new MULTISET table using DISTINCT/GROUP BY or
- insert the duplicate rows (GROUP BY all columns + HAVING COUNT(*) > 1) into a temp table, delete the rows from the table using the temp table and the re-insert the rows from the temp table

Of course you should avoid to get them into a table beforehand.

Dieter

Enthusiast

Re: DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

Thaks Dieter for ur response and
I am aware of this one that's reason i mentioned earlier itself i need to delete duplicates by using teradata query only
actually in oracle we can delete by using rowid but this concept is not possible in teradata .
Is there any alternate way for deleting deuplicates apart from creating set and tempoarary tables.
Enthusiast

Re: DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

Hi Dieter,

We can eliminate dup's from table in follwoing ways.

1.load the data into set table

2.Do the FAST EXPORT and load the data from FAST LOAD
(if Large data available in table i think it is recommend for large tables and it is fast compare to query)

But i need to this using by DELETE query

This below query run in ORACLE, So can u use ROW_NUMBER or ROWID to delete this.

DELETE FROM table_name A WHERE ROWID > ( SELECT min(ROWID) FROM table_name B WHERE A.col = B.col);

Please give me that query and its my requirment.
Junior Contributor

Re: DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

Before the Teradata-ROWID was disabled (for various reasons) you could have written a similar query, of course comparing *all* columns.

But now there's no DELETE query to achieve what you want.

Dieter
Fan

Re: DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

It can be done by adding a temporary column and once the duplicates were removed you can delete that column.And with this there is no need to create a temporary table.

You can refer this thread http://forums.teradata.com/forum/enterprise/key-level-duplicates#comment-16409 and the solution was already there

Cheers :)
Enthusiast

Re: DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

@akolla,

The point here is, is there a way to do this through DML only. Looks like the suituation here is, in production system we need to cleanup the data but we don have the acces to create DDL.

Junior Supporter

Re: DELETING DUPLICATES I NEED SQL QUERY IN TERADATA

The solution posted by akolla is taken from here:

http://carlosal.wordpress.com/2009/12/07/borrando-filas-duplicadas-en-teradata/

If you cannot use 'ALTER TABLE' you could use the identifier column ('X') as the 'flag' field if you can find a range of IDs not used (f. ex.: negative numbers, multiply x some value, etc...)

HTH.

Cheers.

Carlos.