SQL Queries - Common SQL Queries - How to deduplicate a table

Purpose of Query

This page provides queries on how to deduplicate a table or to modify a current table so that all duplicates in it are removed.

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server✅
Microsoft SQL Server❓
CSS✅

Query for CSS

CREATE TABLE TABLE_SAV AS
SELECT *
FROM   TABLE_INITIAL;

DELETE
FROM   TABLE_INITIAL;

INSERT
INTO   TABLE_INITIAL
SELECT DISTINCT *
FROM            TABLE_SAV;

DROP TABLE TABLE_SAV;

Query result example

No results provided - just confirmation of actions

Query result interpretation

  • Create temporary table that has all values
  • Delete current table values
  • Insert into current table only distinct values
  • Drop temporary table

Query for Oracle

CREATE TABLE TABLE_SAV AS
SELECT *
FROM   TABLE_INITIAL;

COMMIT;

DELETE
FROM   TABLE_INITIAL;

COMMIT;

INSERT
INTO   TABLE_INITIAL
SELECT DISTINCT *
FROM            TABLE_SAV;

COMMIT;

DROP TABLE TABLE_SAV;

COMMIT;

Query result example

No results provided - just confirmation of actions

Query result interpretation

  • Create temporary table that has all values
  • Delete current table values
  • Insert into current table only distinct values
  • Drop temporary table

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages