How do I delete duplicity rows without PK? (normal select, disctint select)

Question

Here's the situation

select name,surname,address from myTable -- returns 150rows

select distinct name, surname, address from myTable -- returns 60rows

Let's say, that I can't operate with PK. How do I delete duplicate rows?

select name,surname,address from myTable
MINUS
select distinct name, surname,address from myTable

is not working... MINUS delete everything

Thanks for any advice

Repeat after me: "On my honor as a database developer and user I swear/affirm/promise that I will always, Always, ALWAYS define a primary key on every table I create - even when I'm being too effing smart for my own good and just *know* that this time I don't really need one - so help me Codd". — Bob Jarvis - Слава Україні, Jul 24 '13 at 22:38

score 0 · Answer 1 · answered Jul 24 '13 at 12:51

0

Please try:

delete FROM 
   myTable A
WHERE 
  a.rowid > 
   ANY (
     SELECT 
        B.rowid
     FROM 
        myTable B
     WHERE 
        A.name = B.name AND
        A.surname=B.surname AND
        A.address=B.address
        );

Note: I haven't tested this.

answered Jul 24 '13 at 12:51

TechDo

18,398
3
51
64

1

Testing **DELETE**-Queries is highly overrated! ;) – AKDADEVIL Jul 24 '13 at 13:21
Yeah... :) Exactly! But I made this query from a working example. – TechDo Jul 24 '13 at 13:28

score 0 · Answer 2 · answered Jul 24 '13 at 12:53

0

How about a new table?

create myTable2 as (select distinct name, surname, address from myTable);
drop myTable;

answered Jul 24 '13 at 12:53

etzourid

331
4
18

How do I delete duplicity rows without PK? (normal select, disctint select)

2 Answers2