Deleting Rows

[flixer]

When i delete a duplicate entry in table data it deletes all rows of the same data, i want to keep one of the rows, how can i do this?

[peterlaursen]

you cannot!

when you delete you execute SQL like “DELETE FROM .. WHERE … “

How would you write a WHERE-condition that distinguishes between identical rows? You cannot, and SQLyog cannot either!

That is one good reason to have a PRIMARY KEY in the table!

http://webyog.com/faq/28_70_en.html

Simply add a autoincrement INTEGER column to the table and define that column as the PK – and there will NEVER be identical rows!

[flixer]

peterlaursen wrote on Sep 24 2006, 07:34 PM:

you cannot!

when you delete you execute SQL like “DELETE FROM .. WHERE … “

How would you write a WHERE-condition that distinguishes between identical rows? You cannot, and SQLyog cannot either!

That is one good reason to have a PRIMARY KEY in the table!

http://webyog.com/faq/28_70_en.html

Simply add a autoincrement INTEGER column to the table and define that column as the PK – and there will NEVER be identical rows!

Thankyou, i now understand the importance of a Primary Key!

[TomBez]

flixer wrote on Sep 24 2006, 08:40 PM:

Thankyou, i now understand the importance of a Primary Key!

But you can do somekind of a trick.

alter the table and add a new field, define that field as primary with autoincrement. afterwards you will have all rows numbered and can select all fields where you have double entries and delete them except of the last one.

[Author]

Posts