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.
WHERE clause
Limits the rows of data to be deleted. For more information, see WHERE.
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.)
NULL Values
When NULL values exist in the table, the EQNULL setting must be set to ON to ensure duplicates are removed.
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
The following deletes the duplicate rows based on the transid and empid columns in the transmaster table, where transaction dates are greater than January 1, 2020.
DELETE DUPLICATES FROM TransMaster +
USING TransID, EmpID +
WHERE TransDate > 01/01/2020)
