DELETE DUPLICATES

DELETE DUPLICATES (Short Name: DEL DUP)

Use the DELETE DUPLICATES command to remove duplicate rows from a table.

Options

FROM tblname
Specifies the table name.

USING collist
Deletes rows based on duplicate values in the specified list of columns.

About the DELETE DUPLICATES Command

Use DELETE DUPLICATES to delete duplicate rows from a table. A duplicate row is a row where the values for each column are exactly the same as those in another row in the table. This command deletes all but the first row for each set of duplicate rows.

DELETE DUPLICATES processes faster when the table contains an indexed column and the USING collist option is used.

Rules for Column Deletion

You can specify which rows to delete in a list of columns. The following rules apply:

The first row is retained in the table.

Any row with duplicate values in a specified column list is deleted, regardless of the values in any of its other columns.

Case Sensitivity

DELETE DUPLICATES is case sensitive when CASE is set on. For example, if CASE is set on and one row included the name SMITH and another row included the name Smith, R:BASE would not delete either row. However, if CASE was set off, R:BASE would delete the second row. (The default setting for CASE is off.)

You must restore deleted rows from a backed up database or table. To recover the data's disk space after rows are deleted, use PACK or RELOAD.

If you have set transaction processing on, you can restore rows with ROLLBACK. If not, you must restore them from a backup database or table. If you prefer not to use transaction processing, you can first use a relational command, such as PROJECT to make a backup copy of the table from which you are deleting rows. Then you can delete rows from the original table and remove the backup copy later.

Example

The following command deletes duplicate rows from the transmaster table, but retains the first of the duplicate rows.

DELETE DUPLICATES FROM transmaster

The following example deletes duplicate rows based on the transid, empid, and custid columns in the transmaster table. Only the designated columns will be used to determine whether the rows are duplicates.

DELETE DUPLICATES FROM transmaster USING transid, empid, custid