Write a single SQL to delete duplicate records from the a
single table based on a column value. I need only Unique
records at the end of the Query.
- 13 Answers
- 55599 Views
- I also Faced
- E-Mail Answers
Answer Posted / yuvaevergreen
If atleast one column is distinct, we can delete using
delete statement.
EMPLOYEE TABLE:
EMPNO EMPNAME DEPT
1 YUVA SCI
2 YUVA SCI
DELETE FROM EMPLOYEE WHERE
(EMPNO, EMPNAME,DEPT)
NOT IN
(SELECT EMPNO, EMPNAME,DEPT FROM EMPLOYEE
QUALIFY ROW_NUMBER() OVER
(PARTITION BY EMPNO
ORDER BY EMPNO,EMPNAME,DEPT ASC ) = 1 );
If all the columns are same, then create and drop would be used.
EMPNO EMPNAME DEPT
1 YUVA SCI
1 YUVA SCI
CREATE EMP_NEW AS EMP WITH NO DATA;
INSERT INTO EMP_NEW
SELECT EMPNO, EMPNAME,DEPT FROM EMPLOYEE
QUALIFY ROW_NUMBER() OVER
(PARTITION BY EMPNO, EMPNAME,DEPT
ORDER BY EMPNO,EMPNAME,DEPT ASC ) = 1;
DROP TABLE EMP;
RENAME EMP_NEW TO EMP;
| Is This Answer Correct ? | 9 Yes | 6 No |
Post New Answer View All Answers
How to identify ppi columns?
How to explain project Architecture and flow in teradata interviews?Can please anyone help on this? Am new to teradata.
What are the functions of a Parser?
If Fast Load Script fails and only the error tables are made available to you, then how will you restart?
What is meant by a Virtual Disk?
What is the purpose of joins in teradata and what are the available join types?
What is bteq script in teradata?
What interface is used to connect to windows based applications?
What is the difference between global temporary tables and volatile temporary tables?
How to load specific no.of records using bteq, or fastload,or multiload
Why teradata is used?
Explain fastload in teradata?
What are the various reporting tools in the market?
What are the different methods ot loading a dimension table? A fact table etc?
What are the different table types that are supported by teradata?
