How to Delete duplicate rows without Primary key

Question

The following table has no primary key, I would like to delete the duplicate rows from the following table. The only differentiating factor in the duplicate rows is atom_id2. How do I delete that duplicate row. Please do give your suggestions.

atom_id1, atom_id2, atom_ty1, atom_ty2, lennard_jones_acoef, lennard_jones_bcoef 
---------------------------------------------------------------------------------
1,        1,        N3,       N3,       9.44293233E+05,      8.01323529E+02
1,        2,        N3,       H,        2.12601181E+03,      2.09604198E+01
1,        3,        N3,       H,        2.12601181E+03,      2.09604198E+01
1,        4,        N3,       H,        2.12601181E+03,      2.09604198E+01  <-Duplicate Row
1,        5,        N3,       CX,       9.95480466E+05,      7.36907417E+02
1,        6,        N3,       HP,       2.01791425E+04,      6.45756063E+01
1,        7,        N3,       CT,       9.95480466E+05,      7.36907417E+02
1,        8,        N3,       HC,       8.96776989E+04,      1.36131731E+02
1,        9,        N3,       HC,       8.96776989E+04,      1.36131731E+02 <---- Duplicate Row
1,        10,       N3,       C,        8.82619071E+05,      6.53361429E+02
1,        11,       N3,       O2,       6.06829342E+05,      6.77220874E+02

@zerkms: what If table already contains IDENTITY column ? in that case PK cannot be created. — Muflix, Oct 18 '16 at 11:26

score 2 · Answer 1 · answered Sep 21 '12 at 02:58

Without a primary key, you'll need to provide additional criteria to ensure you delete the appropriate record.

You can do so with the WHERE clause, for example:

DELETE FROM your_table WHERE atom_id1 = 1 AND atom_id2 = 4;

Note: This assumes there are no other records than those you provided. I strongly suggest running this as a SELECT first.

You should also consider creating a primary key. Seems as though atom_id2 might be a good candidate.

score 1 · Answer 2 · answered Sep 21 '12 at 04:21

First you get distinct row insert temp.. table then delete current table and import row from temp.. table.

insert into temp_table(column1,column2,..) 
select distinct column1,column2,... from table2

delete from table2

insert into table2(column1,column2,..) 
select column1,column2,... from temp_table

score 0 · Answer 3 · answered Sep 21 '12 at 03:50

Using Primary Key is the best method. In you case, use atleast two fields in where condition including atom_id2, so that no duplicate will be selected. This you have to confirm first by creating an appropriate SELECT statement and then convert this statement to DELETE which will be easier for you.

score 0 · Answer 4 · answered Sep 21 '12 at 05:45

0

if you find difficulty in deleting duplicate rows in the existing table,you can create new table and insert the distinct values from the existing table. i hope it will help u

answered Sep 21 '12 at 05:45

Pavi

335
2
3
20

How to Delete duplicate rows without Primary key

4 Answers4