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
- 55723 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 are the different table types that are supported by teradata?
What are the various reporting tools in the market?
What is a three-tier data warehouse?
What is difference between user and database in teradata?
What is the primary index in teradata?
What is a level of granularity of a fact table?
How to view every column and the columns contained in indexes in teradata?
how can we analyze the locks ?
What are the various indexes in teradata? How to use them?
How is MLOAD Client System restarted after execution?
Highlight a few of the important components of Teradata?
How do you do backup and recovery in teradata?
Is multi insert ansi standard?
How many types of joins are there in teradata?
Why are oltp database designs not generally a good idea for a data warehouse?
