We may come across a situation where we have duplicate records in a table and we need to remove the duplicates. That is the table must hold only one instance of each row and there should not be any duplicates.In Teradata, this can be done easily by creating a new replica SET table and inserting the data into the new SET table from table which is having duplicate records. The SET table does not allow duplicate records hence all the duplicate records will be discarded and only one instance of each row will be inserted into the table. You can use below query:
CREATE TABLE NEW_TABLE as OLD_TABLE with DATA;
By Default in Teradata, tables are created in SET mode , hence a new table will be created with SET option and while inserting the records from OLD_TABLE into NEW_TABLE , duplicate records will be ignored.So NEW_TABLE will be having only one instance of each row. Once it is done, you can drop your OLD_TABLE and rename NEW_TABLE to OLD_TABLE.
2 thoughts on “Easiest method to delete duplicate records from Table in Teradata”
Of Note: this approach doesn't work when the existing table (OLD_TABLE) has Identity Columns.
Hi AJ
If the old_table has identity column, how come it will have duplicate rows ?
Isn't the idea of creating identity column is to have uniqueness for each row.
Best
Raj