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
- 59973 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
What is a dimension table?
Explain fallback in teradata?
How to Extract data from multiple legacy systems?
What is a sparse index?
What are the newly developed features of Teradata?
Explain how spool space is used.
Why is the case expression used in teradata?
Backup Script was blocked then you are unable to archive the data. how do you analyze it and where do you identify ?
How many sessions of MAX is PE capable of handling at a particular time?
What do you mean by ttu in teradata?
Mention the procedure via which, we can run Teradata jobs in a UNIX environment.
What are the various reporting tools in the market?
What are the frequently used data types in teradata?
How can bottlenecks be identified?
What is bteq utility in teradata?
