Please enable JavaScript to view this site.

R:BASE 11 Help

Navigation: Command Index > D


Scroll Prev Top Next More

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






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.




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.




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




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.



USING TransID, EmpID +

WHERE TransDate > 01/01/2020)