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
- 59022 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
Why does varchar occupy 2 extra bytes?
Highlight a few of the important components of Teradata?
Where is teradata rdbms used?
How to write the query . eid enm doj dob i want to display the names who worked more than 25 years .
If I wanted to run a TPump job only once per day - basically working on a file that is produced once per day - how would you set up the parameters for that sort of job ?
What is the maximum number of dml can be coded in a multiload script?
What does sleep function does in fast load?
Explain the term 'row' related to relational database management system?
List out some of the primary characteristics of teradata.
Hi send me the Teradata dumps to my id rajeshanantha@yahoo.co.in Thanks Rajesh. A
Explain fallback in teradata?
Give a justifiable reason why Multi-load supports NUSI instead of USI.
What is a node in teradata? Explain
What are the enhanced features in teradata v2r5 and v2r6?
Explain teradata architecture?
