Remove Duplicate Rows
In this example, the table named has_dups has duplicate rows that are deleted. This example creates one table based upon the contents of another.
CREATE TABLE temp AS
SELECT DISTINCT * FROM has_dups;
DROP has_dups;
CREATE TABLE has_dups AS
SELECT * FROM temp
WITH NODUPLICATES
DROP temp;
To do the same operation in Visual DBA, follow these steps:
1. Create a new table named temp.
2. Enable Create Table As Select in the Create Table dialog.
3. In the Select Statement edit control, enter:
select distinct * from has_dups
4. Drop the has_dups table.
5. Create a new table named has_dups, using the Options dialog to disable the Duplicates check box.
6. Enable Create Table As Select in the Create Table dialog.
7. In the Select Statement edit control, enter:
select * from temp
8. Drop the temp table.
Note: If a table was originally created to allow duplicate rows, but you no longer want the table to allow duplicate rows, you must perform Steps 1-8 above. However, because duplicate row checking is only performed for tables having a keyed structure, you must also perform the additional step of modifying the table to a keyed structure (hash, ISAM, or B-tree).