DEDUP deletes all but the first row for each key value set, sorted on the key columns and then also the non-key columns.

Syntax


DEDUP /*+ … */ target [WITH KEY (col1,...)]

Results

  • The count of deleted rows.

Arguments

  • /*+ … */ - Insert optional Cirro Hint.

    • Since there is no data movement, most hints are irrelevant.
  • source - source datasource in form system.schema.database

  • target - target datasource in form system.schema.database

  • WITH KEY - If the key is not given, the primary key of the target is used. If KEY is not given for a table DEDUP loos for the table’s primary key.

Additional Information

  • Creation of auto-increment columns is not currently supported by Cirro. You need either auto-increment or row_number() to create a column to differentiate identical records.

Examples

Example tables are drawn from the imdb database, for instance:

  • cast_info (id, person_id, movie_id, person_role_id, note, nr_order, role_id)

  • movie_keyword (id, movie_id, keyword_id)

  • company_name(id, name, country_code, imdb_id, name_pcode_nf, name_pcode_sf)

DEDUP target [WITH KEY { (col1[,…]) | (*) }]

This uses the output of DUPLICATES to delete all but the first record from the target table.

The primary key, id, is unique so no rows are deleted.

DEDUP lim.imdb.public.role_type ;

Make (movie_id, keyword_id) a unique key.

DEDUP lim.imdb.public.movie_keyword WITH KEY (movie_id, keyword_id) ;

This will delete all but one of the identical rows.

DEDUP lim.imdb.public.role_type WITH KEY (*) ;