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;

Hmmm. 6200 views, but no one found any of my answers useful? — maf-soft, Jun 12 '15 at 14:56
...but I really don't understand the votes to the answers. Don't people like it when someone answers their own questions? @zx8754 — maf-soft, Feb 09 '23 at 07:29
I don't know sql deep enough to know which answer is the right one, we can only assume/hope people who voted do. — zx8754, Feb 09 '23 at 08:53

score 7 · Answer 1 · edited Dec 23 '15 at 22:52

7

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

edited Dec 23 '15 at 22:52

Mayur Pawar

107
2
16

answered Oct 21 '14 at 17:17

Karen Burton

71
1
2

What if I have millions of records and just a few duplicates? It is a straightforward and easy to remember solution, if you once and manually need to get rid of duplicates. But it could be much slower, needs a temporary table, and is not exactly what was asked for. In addition, the title already says there is no ID (primary key) - if you have an ID, you could anyway use many other solutions which require a primary key. – maf-soft Oct 21 '14 at 18:48
A `select` and some `;` are missing :) – maf-soft Oct 21 '14 at 18:51

maf-soft · Answer 2 · 2013-10-24T07:09:04.137

2

This works well and fast:

DELETE FROM #TestTable
WHERE ROWID(#TestTable) IN (
  SELECT rowid FROM (
    SELECT ROWID(#TestTable) rowid, 
      ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY 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.

edited Oct 24 '13 at 07:09

answered Oct 23 '13 at 14:25

maf-soft

2,335
3
26
49

The ROW_NUMBER() OVER(PARTITION BY XX) function is only valid for Adaptive Server Enterprise. – user3407335 Feb 27 '23 at 21:17
It worked with Sybase Anywhere 11 – maf-soft Apr 19 '23 at 06:44

score 0 · Answer 3 · edited Oct 21 '14 at 18:50

0

Please try this:

create clustered index i1 on table table_name(column_name) with ignore_dup_row

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

edited Oct 21 '14 at 18:50

honk

9,137
11
75
83

answered Oct 23 '13 at 15:11

Gopal Sanodiy

91
2

Thanks, it's quite interesting to learn about Sybase options ignore_dup_row and allow_dup_row. But it doesn't seem to work with my Sybase version: there is no error when creating the index, but the option seems to be ignored and the select returns both rows. – maf-soft Oct 29 '13 at 07:23
Looks like it is only valid for Adaptive Server Enterprise (Sybase ASE) - I just wonder why it doesn't give any error. – maf-soft Oct 29 '13 at 07:34

maf-soft · Answer 4 · 2013-10-29T09:16:40.843

Here is another interesting one I found and adopted:

DELETE FROM #TestTable dupes
FROM #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 dupes
FROM #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 dupes
FROM #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.

Yes, joining the row IDs with "greater than" is quite cool and very valid to surprise a colleague ;-) - but when you think about what this really does... I feel like a sadistic db torturer... or could the optimizer be _that_ smart and just laugh at me? — maf-soft, Oct 29 '13 at 09:14

score -1 · Accepted Answer · edited May 23 '17 at 12:15

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:

DELETE FROM #TestTable
FROM #TestTable
LEFT OUTER JOIN (
  SELECT MIN(ROWID(#TestTable)) rowid
  FROM #TestTable
  GROUP BY Column1, Column2
) AS KeepRows ON ROWID(#TestTable) = KeepRows.rowid
WHERE KeepRows.rowid IS NULL;

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

DELETE FROM #TestTable
WHERE ROWID(#TestTable) NOT IN (
  SELECT MIN(ROWID(#TestTable))
  FROM #TestTable
  GROUP BY 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).

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

5 Answers5

Linked