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
- 59515 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 advantages of teradata?
My table got locked during mload due to a failed job. What do I do to perform other operations on it?
What is partitioned primary index (ppi)?
What is the primary index in teradata?
What do you mean by parsing?
What is spool space? Why do you get spool space errors?
What are the functions involved in shared information architecture?
What is the purpose of using case expression in teradata?
How would you load a very large file in teradata in general?
Give a justifiable reason why Multi-load supports NUSI instead of USI.
In a table can we use primary key in one column and in another column both unique and not null constrains.if yes how?
What are the 5 phases in a multiload utility?
In Teradata, what is the significance of UPSERT command?
How do you create tables? Exact syntax, and create profiles, users in teradata?
What can be achieved by using the teradata rdbms?
