Finding, deleting and preventing duplicate rows

View All Scripts Login to Run Script

Script Name Finding, deleting and preventing duplicate rows
Description Examples of SQL queries to: - Find duplicated rows in a table - Delete these - Add a unique constraint to prevent new invalid rows This also shows how you can use the "exceptions into" clause when validating constraints.
Area SQL General
Contributor Chris Saxon (Oracle)
Created Friday January 29, 2016

Statement 1

create table films ( 
  film_id           integer not null primary key, 
  title             varchar2(100) not null, 
  uk_release_date   date not null, 
  length_in_minutes integer not null, 
  bbfc_rating       varchar2(10) not null, 
  insert_date       date not null 
)

Table created.

Statement 2

create table exceptions ( 
  row_id     rowid, 
  owner      varchar2(30), 
  table_name varchar2(30), 
  constraint varchar2(30) 
)

Table created.

Statement 3

insert into films values (1, 'Frozen', date'2013-12-06', 102, 'PG', sysdate-2)

1 row(s) inserted.

Statement 4

insert into films values (2, 'Frozen', date'2013-12-06', 102, 'PG', sysdate-1)

1 row(s) inserted.

Statement 5

insert into films values (3, 'Frozen', date'2013-12-06', 100, 'U', sysdate)

1 row(s) inserted.

Statement 6

insert into films values (4, 'Aladdin', date'1993-11-18', 90, 'U', sysdate-2)

1 row(s) inserted.

Statement 7

insert into films values (5, 'Aladdin', date'1993-11-18', 90, 'U', sysdate-1)

1 row(s) inserted.

Statement 8

insert into films values (6, 'The Little Mermaid', date'1990-10-12', 83, 'U', sysdate)

1 row(s) inserted.

Statement 9
```
commit
```
```
Statement processed.
```

Statement 10

select * 
from   films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
2 Frozen 06-DEC-13 102 PG 28-JAN-16
3 Frozen 06-DEC-13 100 U 29-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
5 Aladdin 18-NOV-93 90 U 28-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

6 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
2	Frozen	06-DEC-13	102	PG	28-JAN-16
3	Frozen	06-DEC-13	100	U	29-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
5	Aladdin	18-NOV-93	90	U	28-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 11

This finds the values of the repeated values. You only get one row per group.

select title, uk_release_date, count(*) 
from   films 
group  by title, uk_release_date 
having count(*) > 1

 
TITLE UK_RELEASE_DATE COUNT(*)
Aladdin 18-NOV-93 2
Frozen 06-DEC-13 3

2 rows selected.

TITLE	UK_RELEASE_DATE	COUNT(*)
Aladdin	18-NOV-93	2
Frozen	06-DEC-13	3

Statement 12

To see all the rows that have duplicates, query the table again. Pass the previous SQL in as a subquery.

select * from films 
where  (title, uk_release_date) in ( 
	select title, uk_release_date 
	from   films 
	group  by title, uk_release_date 
	having count(*) > 1 
)

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
2 Frozen 06-DEC-13 102 PG 28-JAN-16
3 Frozen 06-DEC-13 100 U 29-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
5 Aladdin 18-NOV-93 90 U 28-JAN-16

5 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
2	Frozen	06-DEC-13	102	PG	28-JAN-16
3	Frozen	06-DEC-13	100	U	29-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
5	Aladdin	18-NOV-93	90	U	28-JAN-16

Statement 13

select f.*,  
       count(*) over (partition by title, uk_release_date) ct 
from   films f

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE CT
4 Aladdin 18-NOV-93 90 U 27-JAN-16 2
5 Aladdin 18-NOV-93 90 U 28-JAN-16 2
2 Frozen 06-DEC-13 102 PG 28-JAN-16 3
1 Frozen 06-DEC-13 102 PG 27-JAN-16 3
3 Frozen 06-DEC-13 100 U 29-JAN-16 3
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16 1

6 rows selected.

Statement 14
You can't use an analytic function in the where clause. This statement will error.
```
select f.* 
from   films f 
where  count(*) over (partition by title, uk_release_date) > 1
```
```
ORA-00934: group function is not allowed here
```
More Details: https://docs.oracle.com/error-help/db/ora-00934

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE	CT
4	Aladdin	18-NOV-93	90	U	27-JAN-16	2
5	Aladdin	18-NOV-93	90	U	28-JAN-16	2
2	Frozen	06-DEC-13	102	PG	28-JAN-16	3
1	Frozen	06-DEC-13	102	PG	27-JAN-16	3
3	Frozen	06-DEC-13	100	U	29-JAN-16	3
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16	1

Statement 15

To filter on the results of the analytic, you must apply the where clause in an inline view...

select *  
from   ( 
  select f.*,  
	 count(*) over (partition by title, uk_release_date) ct 
  from   films f 
) 
where  ct > 1

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE CT
4 Aladdin 18-NOV-93 90 U 27-JAN-16 2
5 Aladdin 18-NOV-93 90 U 28-JAN-16 2
2 Frozen 06-DEC-13 102 PG 28-JAN-16 3
1 Frozen 06-DEC-13 102 PG 27-JAN-16 3
3 Frozen 06-DEC-13 100 U 29-JAN-16 3

5 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE	CT
4	Aladdin	18-NOV-93	90	U	27-JAN-16	2
5	Aladdin	18-NOV-93	90	U	28-JAN-16	2
2	Frozen	06-DEC-13	102	PG	28-JAN-16	3
1	Frozen	06-DEC-13	102	PG	27-JAN-16	3
3	Frozen	06-DEC-13	100	U	29-JAN-16	3

Statement 16

... or using the with clause.

with film_counts as ( 
  select f.*, count(*) over (partition by title, uk_release_date) ct 
  from   films f 
) 
  select * 
  from   film_counts 
  where  ct > 1

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE CT
4 Aladdin 18-NOV-93 90 U 27-JAN-16 2
5 Aladdin 18-NOV-93 90 U 28-JAN-16 2
2 Frozen 06-DEC-13 102 PG 28-JAN-16 3
1 Frozen 06-DEC-13 102 PG 27-JAN-16 3
3 Frozen 06-DEC-13 100 U 29-JAN-16 3

5 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE	CT
4	Aladdin	18-NOV-93	90	U	27-JAN-16	2
5	Aladdin	18-NOV-93	90	U	28-JAN-16	2
2	Frozen	06-DEC-13	102	PG	28-JAN-16	3
1	Frozen	06-DEC-13	102	PG	27-JAN-16	3
3	Frozen	06-DEC-13	100	U	29-JAN-16	3

Statement 17

A correlated delete. This will only work if all the values for insert date are unique for every (title, uk_release_date) pair.

delete films f 
where  insert_date not in ( 
  select min(insert_date) 
  from   films s 
  where  f.title = s.title 
  and    f.uk_release_date = s.uk_release_date 
)

3 row(s) deleted.

Statement 18

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

3 rows selected.

Statement 19
```
rollback
```
```
Statement processed.
```

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 20

An uncorrelated delete. This works because film_id is the primary key, so we know it's unique across the whole table.

delete films 
where  film_id not in ( 
  select min(film_id) 
  from   films 
  group  by title, uk_release_date 
)

3 row(s) deleted.

Statement 21

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

3 rows selected.

Statement 22
```
rollback
```
```
Statement processed.
```

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 23

If there wasn't film_id or insert_date columns on this table, you could use rowid to find and remove the duplicates.

delete films 
where  rowid not in ( 
  select min(rowid) 
  from   films 
  group  by title, uk_release_date 
)

3 row(s) deleted.

Statement 24

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

3 rows selected.

Statement 25
```
rollback
```
```
Statement processed.
```

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 26

There are duplicates in the table, so the constraint creation will fail

alter table films add constraint  
  film_u unique (title, uk_release_date)

ORA-02299: cannot validate (SQL_RFAHBKXKIBULIMTCPCVTMHRZA.FILM_U) - duplicate keys found

More Details: https://docs.oracle.com/error-help/db/ora-02299

Statement 27

Novalidate doesn't work as expected; Oracle still creates a unique index in the background

alter table films add constraint  
  film_u unique (title, uk_release_date) novalidate

ORA-02299: cannot validate (SQL_RFAHBKXKIBULIMTCPCVTMHRZA.FILM_U) - duplicate keys found

More Details: https://docs.oracle.com/error-help/db/ora-02299

Statement 28

You need to create the non-unqiue index explicitly. Or use an already existing (non-unique) index.

alter table films add constraint  
  film_u unique (title, uk_release_date)  
  using index ( 
    create index film_i on films (title, uk_release_date) 
  ) novalidate

Table created.

Statement 29

So you can't insert duplicates anymore

insert into films values (7, 'Frozen', date'2013-12-06', 102, 'PG', sysdate-1)

ORA-00001: unique constraint (SQL_RFAHBKXKIBULIMTCPCVTMHRZA.FILM_U) violated

More Details: https://docs.oracle.com/error-help/db/ora-02299

Statement 30

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
2 Frozen 06-DEC-13 102 PG 28-JAN-16
3 Frozen 06-DEC-13 100 U 29-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
5 Aladdin 18-NOV-93 90 U 28-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

6 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
2	Frozen	06-DEC-13	102	PG	28-JAN-16
3	Frozen	06-DEC-13	100	U	29-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
5	Aladdin	18-NOV-93	90	U	28-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 31

Saving exceptions stores the violating rowids into the exceptions table

alter table films modify constraint  
  film_u validate exceptions into exceptions

ORA-02299: cannot validate (SQL_RFAHBKXKIBULIMTCPCVTMHRZA.FILM_U) - duplicate keys found

More Details: https://docs.oracle.com/error-help/db/ora-02299

Statement 32

select * from exceptions

 
ROW_ID OWNER TABLE_NAME CONSTRAINT
AAAUuMAAXAAAADHAAC SQL_RFAHBKXKIBULIMTCPCVTMHRZA FILMS FILM_U
AAAUuMAAXAAAADHAAB SQL_RFAHBKXKIBULIMTCPCVTMHRZA FILMS FILM_U
AAAUuMAAXAAAADHAAA SQL_RFAHBKXKIBULIMTCPCVTMHRZA FILMS FILM_U
AAAUuMAAXAAAADHAAE SQL_RFAHBKXKIBULIMTCPCVTMHRZA FILMS FILM_U
AAAUuMAAXAAAADHAAD SQL_RFAHBKXKIBULIMTCPCVTMHRZA FILMS FILM_U

5 rows selected.

ROW_ID	OWNER	TABLE_NAME	CONSTRAINT
AAAUuMAAXAAAADHAAC	SQL_RFAHBKXKIBULIMTCPCVTMHRZA	FILMS	FILM_U
AAAUuMAAXAAAADHAAB	SQL_RFAHBKXKIBULIMTCPCVTMHRZA	FILMS	FILM_U
AAAUuMAAXAAAADHAAA	SQL_RFAHBKXKIBULIMTCPCVTMHRZA	FILMS	FILM_U
AAAUuMAAXAAAADHAAE	SQL_RFAHBKXKIBULIMTCPCVTMHRZA	FILMS	FILM_U
AAAUuMAAXAAAADHAAD	SQL_RFAHBKXKIBULIMTCPCVTMHRZA	FILMS	FILM_U

Statement 33

This will delete all the duplicated rows. In most cases you'll want to keep one, so you still need the check to exclude all but one

select * from films 
where  rowid in ( 
  select row_id from exceptions  
  where  table_name = 'FILMS'  
  and    constraint = 'FILM_U'  
  and    owner = sys_context('userenv', 'current_user') 
)

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
2 Frozen 06-DEC-13 102 PG 28-JAN-16
3 Frozen 06-DEC-13 100 U 29-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
5 Aladdin 18-NOV-93 90 U 28-JAN-16

5 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
2	Frozen	06-DEC-13	102	PG	28-JAN-16
3	Frozen	06-DEC-13	100	U	29-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
5	Aladdin	18-NOV-93	90	U	28-JAN-16

Statement 34

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

Statement 35
```
rollback
```
```
Statement processed.
```

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 36

Like this. The exceptions subquery in the "rowid not in" clause prevents a full table scan of films. This may improve performance if there's only a few exceptions (relative to the number of rows in films).

delete films 
where  rowid in ( 
  select row_id from exceptions  
  where  table_name = 'FILMS'  
  and    constraint = 'FILM_U'  
  and    owner = sys_context('userenv', 'current_user') 
) 
and    rowid not in ( 
  select min(rowid) 
  from   films 
  where  rowid in ( 
    select row_id from exceptions  
    where  table_name = 'FILMS'  
    and    constraint = 'FILM_U'  
    and    owner = sys_context('userenv', 'current_user') 
  ) 
  group  by title, uk_release_date 
  having count(*) > 1 
)

3 row(s) deleted.

Statement 37

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

3 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 38

The ignore hint means the insert silently fails. It needs a unique index to work though, so this still throws an exception

insert /*+ ignore_row_on_dupkey_index (films(title, uk_release_date)) */ into films  
values (7, 'Frozen', date'2013-12-06', 102, 'PG', sysdate)

ORA-38913: Index specified in the index hint is invalid

More Details: https://docs.oracle.com/error-help/db/ora-38913

Statement 39

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

3 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Statement 40

You can do this by creating an invisiable unique index...

create unique index film_ui on films (title, uk_release_date) invisible

Index created.

Statement 41

... dropping the original constraint ...

alter table films drop constraint film_u

Table altered.

Statement 42
... and index.
```
drop index film_i
```
```
Index dropped.
```

Statement 43

Make the new index visible

alter index film_ui visible

Statement processed.

Statement 44

... and re-create the constraint

alter table films add constraint film_u unique (title, uk_release_date) using index film_ui

Table altered.

Statement 45

With the unique index in place, the hints works and this fails silently!

insert /*+ ignore_row_on_dupkey_index (films(title, uk_release_date)) */ into films  
values (7, 'Frozen', date'2013-12-06', 102, 'PG', sysdate)

0 row(s) inserted.

Statement 46

select * from films

 
FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATING INSERT_DATE
1 Frozen 06-DEC-13 102 PG 27-JAN-16
4 Aladdin 18-NOV-93 90 U 27-JAN-16
6 The Little Mermaid 12-OCT-90 83 U 29-JAN-16

3 rows selected.

FILM_ID	TITLE	UK_RELEASE_DATE	LENGTH_IN_MINUTES	BBFC_RATING	INSERT_DATE
1	Frozen	06-DEC-13	102	PG	27-JAN-16
4	Aladdin	18-NOV-93	90	U	27-JAN-16
6	The Little Mermaid	12-OCT-90	83	U	29-JAN-16

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database