How to delete duplicate rows in sybase, when you have no unique key?

Question

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 even found some Sybase-related questions marked as duplicates for SQL Server questions, which doesn't help.

One example for solutions I liked, but didn't work, is the WITH ... DELETE ... construct.

I have found working solutions using cursors or while-loops, but I hope it is possible without loops.

I hope for a nice, simple and fast query, just deleting all but one exact duplicate.

Here a little framework for testing:

IF OBJECT_ID( 'tempdb..#TestTable' ) IS NOT NULL
  DROP TABLE #TestTable;

CREATE TABLE #TestTable (Column1 varchar(1), Column2 int);

INSERT INTO #TestTable VALUES ('A', 1);
INSERT INTO #TestTable VALUES ('A', 1); -- duplicate
INSERT INTO #TestTable VALUES ('A', 1); -- duplicate
INSERT INTO #TestTable VALUES ('A', 2);
INSERT INTO #TestTable VALUES ('B', 1);
INSERT INTO #TestTable VALUES ('B', 2);
INSERT INTO #TestTable VALUES ('B', 2); -- duplicate
INSERT INTO #TestTable VALUES ('C', 1);
INSERT INTO #TestTable VALUES ('C', 2);

SELECT * FROM #TestTable ORDER BY Column1,Column2;

DELETE <your solution here>

SELECT * FROM #TestTable ORDER BY Column1,Column2;

Answer 1

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

select distinct * 
into #temp_table
from table_with_duplicates 

delete table_with_duplicates 

insert into 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