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

Created by John McMahon on Feb 11, 2022
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
Release
Yes/No
8.3.x(tick)
8.2.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(tick)
Microsoft SQL Server(question)
CSS(tick)
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