----- Original Message ----- From: "A Z" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 21, 2004 10:02 AM Subject: Duplicate Rows
> > > MySQL 4.0.14 > > In a scenario: > > Ref EmailAddr > ---------------- > 1 [EMAIL PROTECTED] > 2 [EMAIL PROTECTED] > 3 [EMAIL PROTECTED] > 4 [EMAIL PROTECTED] > > how can I delete duplicate email entries (records 1, > 2) leaving 4. > The normal design for *any* table in a relational database is to have a primary key on the table. That ensures that every row in the table can be uniquely identified and accessed, independently of all the others. The primary key may consist of one column or several. In the case of a single column primary key, the value of that column must be unique in every row of the table and the database will ensure that no duplicates are ever stored. If the primary key has several columns, the *combination* of values in the primary key must always be unique on every row in the table and the database will not let you add a row that duplicates the primary key of an existing row. It isn't clear from your question if you established a primary key since you don't provide a definition of the table. If you *did* create a primary key, I'm assuming that the Ref column is it. In this case, the following will do the job: delete from your_table where ref in (1,2) If some other column that you aren't showing was the primary key, use it in the Where clause instead and specify the key values of the rows you want to delete. If you didn't specify a primary key, which I suspect is the case, and the 'Ref' column is just something you added so we'd know which rows you want to delete, there is no simple way to do the delete. You *could* write an application that reads each row in a loop, displays it to you, and then asks if that row should be deleted, then only delete it if the user answers 'yes'. Short of that, I can't think of any way to do it. In that case, you should learn from this and remember to put primary keys on every table that you create from now on - and retrofit them to all of your existing tables. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
