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
– Muflix, Commented Oct 18, 2016 at 11:26

Jason McCreary · Accepted Answer · 2012-09-21 02:58:56Z

2

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.

answered Sep 21, 2012 at 2:58

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vikram Jain · Accepted Answer · 2012-09-21 04:21:59Z

1

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

answered Sep 21, 2012 at 4:21

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Comments

Anish V · Accepted Answer · 2012-09-21 03:50:49Z

0

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.

answered Sep 21, 2012 at 3:50

Anish V

6833 gold badges17 silver badges40 bronze badges

Comments

Pavi · Accepted Answer · 2012-09-21 05:45:34Z

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, 2012 at 5:45

Pavi

3552 gold badges3 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to Delete duplicate rows without Primary key

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related