How To Delete Duplicate Rows In Sybase, When You Have No Unique Key?

March 21, 2024 Post a Comment

Yes, you can find similar questions numerous times, but: the most elegant solutions posted here, work for SQL Server, but not for Sybase (in my case Sybase Anywhere 11). I have eve

Solution 1:

If all fields are identical, you can just do this:

selectdistinct*into #temp_table
from table_with_duplicates 

delete table_with_duplicates 

insertinto table_with_duplicates select*from #temp_table

If all fields are not identical, for example, if you have an id that is different, then you'll need to list all the fields in the select statement, and hard code a value in the id to make it identical, if that is a field you don't care about. For example:

insert #temp_table field1, field2, id select (field1, field2, 999)
from table_with_duplicates

Solution 2:

This works well and fast:

DELETEFROM #TestTable
WHERE ROWID(#TestTable) IN (
  SELECT rowid FROM (
    SELECT ROWID(#TestTable) rowid, 
      ROW_NUMBER() OVER(PARTITIONBY Column1,Column2 ORDERBY Column1,Column2) rownum
    FROM #TestTable
  ) sub
  WHERE rownum >1
);

If you don't know OVER(PARTITION BY ...), just execute the inner SELECT statement to see what it does.

Solution 3:

Here is another interesting one I found and adopted:

DELETE FROM#TestTable dupesFROM#TestTable dupes, #TestTable fullTable
WHERE dupes.Column1 = fullTable.Column1
  AND dupes.Column2 = fullTable.Column2
  AND ROWID(dupes) > ROWID(fullTable);

or, if you like explicit joins more (I do):

DELETE FROM#TestTable dupesFROM#TestTable dupes
INNER JOIN #TestTable fullTable
  ON dupes.Column1 = fullTable.Column1
  AND dupes.Column2 = fullTable.Column2
  AND ROWID(dupes) > ROWID(fullTable);

or the short form (a "natural" join incorporates identical column names automatically):

DELETE FROM#TestTable dupesFROM#TestTable dupes
NATURAL JOIN #TestTable fullTable
  ON ROWID(dupes) > ROWID(fullTable);

...if someone finds a solution not requiring ROWID(), I would be interested to see them.

Solution 4:

Please try this:

create clustered index i1 ontable table_name(column_name) with ignore_dup_row

createtable #test(id int,name char(9))
insertinto #test values(1,"A")
insertinto #test values(1,"A")
create clustered index i1 on #test(id) with ignore_dup_row
select*from #test

Solution 5:

Ok, now that I know the ROWID() function, solutions for tables with primary key (PK) can be easily adopted. This one first selects all rows to keep and then deletes the remaining ones:

DELETEFROM #TestTable
FROM #TestTable
LEFTOUTERJOIN (
  SELECTMIN(ROWID(#TestTable)) rowid
  FROM #TestTable
  GROUPBY Column1, Column2
) AS KeepRows ON ROWID(#TestTable) = KeepRows.rowid
WHERE KeepRows.rowid ISNULL;

...or how about this shorter variant? I like!

DELETEFROM #TestTable
WHERE ROWID(#TestTable) NOTIN (
  SELECTMIN(ROWID(#TestTable))
  FROM #TestTable
  GROUPBY Column1, Column2
);

In this post, which inspired me most, is a comment that NOT IN might be slower. But that's for SQL server, and sometimes elegance is more important :) - I also think it all depends on good indexes.

Anyway, usually it is bad design, to have tables without a PK. You should at least add an "autoinc" ID, and if you do, you can use that ID instead of the ROWID() function, which is a non-standard extension by Sybase (some others have it, too).

Lab Sql