473,735 Members | 8,833 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Deleting duplicate entries from MySQL database table

The topic is related to MySQL database.

Suppose a table "address" contains the following records

-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
| mr x | 8th lane | 124364 |
-------------------------------------------------------
>Execute single query (MySQL Version: No Restriction), with sub-query or some other method
>After executing the query
-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
-------------------------------------------------------
>Please suggest on this
Jul 7 '06 #1
5 3989
*** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
>>Please suggest on this
If you don't make a question I don't really what you kind of suggestions
you are expecting. Anyway, the best way to avoid duplicate information is
having a good design so the database is normalised and using primary keys
and unique indexes so the dupes cannot be physically inserted.
--
-+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
++ Mi sitio sobre programación web: http://bits.demogracia.com
+- Mi web de humor con rayos UVA: http://www.demogracia.com
--
Jul 7 '06 #2
I think he wants to get a distinct set of all the addresses.

Use the group by clause or the distinct modifier.

Alvaro G. Vicario wrote:
*** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
>Please suggest on this

If you don't make a question I don't really what you kind of suggestions
you are expecting. Anyway, the best way to avoid duplicate information is
having a good design so the database is normalised and using primary keys
and unique indexes so the dupes cannot be physically inserted.
--
-+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
++ Mi sitio sobre programación web: http://bits.demogracia.com
+- Mi web de humor con rayos UVA: http://www.demogracia.com
--
Jul 7 '06 #3
Sorry for not formulating the question properly.

The scenario is as:
1. A table is created
2. No Primary Key, Unique Key, Validation process.
3. Various duplicate entries gor inserted in the table (all fields
contain same data)

May be programming login bug.

In the edit process, nothing is edited, (all data are same), and
instead of updating the record, new row get inserted.

4. Now it is desired that all the entries with duplicate entries gets
deleted abd only 1 record (within duplicate entry) remains in the
table.

As in shown in example.

Suppose a table "address" contains the following records

-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
| mr x | 8th lane | 124364 |
-------------------------------------------------------
>Execute single query (MySQL Version: No Restriction), with sub-query or some other method
After executing the query
-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
-------------------------------------------------------

Here instead of deleting both duplicate entry

| mr x | 8th lane | 124364 |

only one gets deleted (out of two) and no duplicate entries are there
in table.

Hope it will clarify my question.

Thanks.

Manish
Alvaro G. Vicario wrote:
*** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
>Please suggest on this

If you don't make a question I don't really what you kind of suggestions
you are expecting. Anyway, the best way to avoid duplicate information is
having a good design so the database is normalised and using primary keys
and unique indexes so the dupes cannot be physically inserted.
--
-+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
++ Mi sitio sobre programación web: http://bits.demogracia.com
+- Mi web de humor con rayos UVA: http://www.demogracia.com
--
Jul 9 '06 #4
Manish wrote:
The topic is related to MySQL database.

Suppose a table "address" contains the following records

-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
| mr x | 8th lane | 124364 |
-------------------------------------------------------

>>>Execute single query (MySQL Version: No Restriction), with sub-query or some other method

>>>After executing the query


-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
-------------------------------------------------------

>>>Please suggest on this

Manish,

Since your question is related to a MySQL database, might I suggest you
ask in a MySQL newsgroup - like comp.databases. mysql?

That's where the MySQL gurus hang out.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Jul 9 '06 #5
Manish wrote:
Sorry for not formulating the question properly.

The scenario is as:
1. A table is created
2. No Primary Key, Unique Key, Validation process.
3. Various duplicate entries gor inserted in the table (all fields
contain same data)

May be programming login bug.

In the edit process, nothing is edited, (all data are same), and
instead of updating the record, new row get inserted.

4. Now it is desired that all the entries with duplicate entries gets
deleted abd only 1 record (within duplicate entry) remains in the
table.

As in shown in example.

Suppose a table "address" contains the following records

-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
| mr x | 8th lane | 124364 |
-------------------------------------------------------
Execute single query (MySQL Version: No Restriction), with sub-query or some other method
After executing the query

-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
-------------------------------------------------------

Here instead of deleting both duplicate entry

| mr x | 8th lane | 124364 |

only one gets deleted (out of two) and no duplicate entries are there
in table.

Hope it will clarify my question.

Thanks.

Manish

Use distinct and create a new table.

INSERT INTO nodupes VALUES SELECT DISTINCT * FROM dupes

the distinct modifier will make sure all the rows of the result set are
different.

Note: this is under the assumption that all fields of the duplicate
rows are the same.

Another way is to create second table and set the primary key to
whatever should be unique part. Then do INSERT INTO IGNORE nodupes
VALUES SELECT * FROM dupes
The ignore portion will ignore any duplicate key errors.
Alvaro G. Vicario wrote:
*** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
>>Please suggest on this
If you don't make a question I don't really what you kind of suggestions
you are expecting. Anyway, the best way to avoid duplicate information is
having a good design so the database is normalised and using primary keys
and unique indexes so the dupes cannot be physically inserted.
--
-+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
++ Mi sitio sobre programación web: http://bits.demogracia.com
+- Mi web de humor con rayos UVA: http://www.demogracia.com
--
Jul 9 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4640
by: Francesco Moi | last post by:
Hello. I've got a 'books' table with 'id', 'name', 'author' and 'isbn' fields. Due to an error, my books are duplicated, so I've got (e.g.): 430 - Moby Dick - Herman Melville - 0192833855 430 - Moby Dick - Herman Melville - 0192833855 528 - A Tale of Two Cities - Charles Dickens - 0141439602 528 - A Tale of Two Cities - Charles Dickens - 0141439602
0
3106
by: Gary Lundquest | last post by:
I have an application with MS Access 2000 as the front end and MySQL as the back end. All was well until I upgraded the MySQL (Linux) server. The Problem: I insert data into a cumulative table. Before when I did this, and there were duplicate entries, the duplicate entries were rejected and I got a return code with the number of affected rows (number added). Now, I get a MyODBC error and the application stops when trying to add...
1
838
by: Gary Lundquest | last post by:
It appears to me that MySQL version 4 returns an error messge when doing an Insert that results in duplicate entries. Version 3 did NOT return an error - it dropped the duplicate entries and ran to completion. Version 4 seems to STOP when it encounters a duplicate entry, so that the records before the duplicate are inserted and the records after the duplicate are not inserted. 3.22.27.1 - previous ver MySQL that did not return error...
1
2988
by: JStrummer | last post by:
I just put together a PHP mailing list sign-up page that posts to a mySQL DB. The PHP script prevents INSERTs when the email address is already located in the database. Problem: I need to import some flat-files that stored the signups, prior to this new form. Email addresses weren't checked, so there are a lot of records that have the same email address. Once I import these into the table, how would I go about putting together a SQL...
18
2479
by: Dan | last post by:
hello, I would to know if it is possible to delete an instance in an array, The following does not allow me to do a delete. I am trying to find and delete the duplicate in an array, thanks for ( j =0; j<MAX ; j++) { for ( i =0; i<MAX ; i++)
3
2756
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the following code: Private Sub Command28_Click() On Error GoTo Err_Command28_Click
0
1866
by: Manish | last post by:
Suppose a table "address" contains the following records ------------------------------------------------------- | name | address | phone | ------------------------------------------------------- | mr x | 8th lane | 124364 | | mr x | 6th lane | 435783 | | mrs x | 6th lane | 435783 | | mr x | 8th lane | 124364 | -------------------------------------------------------
2
3007
by: rimvydazas | last post by:
Hi guys, I have a script which deletes necessary entries on specific date. Here is the script: #!/usr/bin/perl -w use DBI; my $dbh = DBI->connect( "dbi:mysql:test", "newbie", "")
7
2932
by: php_mysql_beginer911 | last post by:
Hi .. i am trying to update a table where if field contents any duplictaed entries than one of the field should be updated with random number which is unique so i can make all entries unique i searched a lot but couldn't find any solution which i could understand easily . is it very difficult in sql to update duplicate entries with new unique random values? table example ----------------------
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9251
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8201
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4559
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4822
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2739
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2188
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.