Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 59605 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
Describe the between keyword in teradata?
Hi Friends, Please send me Teradata Materials for certifications Thanks in advance !!
What is meant by a Channel Driver?
How do you determine the number of sessions?
What is the meaning of Caching in Teradata?
What are the various reporting tools in the market?
What is database exceptions in teradata?
What are the steps to create a data model?
What are the components used in smp and massively parallel processing (mpp) machines?
what is object level locking ? where do appear this type of locking ?
Syntax for case when statement?
Explain and compare pros and cons of snow flake schemas?
What can be achieved by using the teradata rdbms?
What are the string manipulation operators and functions associated with teradata?
Explain the parallel data extension in teradata?
