Solved

Deleting duplicates by title field

Posted on 2014-12-17
12
57 Views
Last Modified: 2014-12-25
How do I delete entries that have any matching field data?
0
Comment
Question by:twittoris
  • 5
  • 4
  • 3
12 Comments
 
LVL 58

Expert Comment

by:Gary
Comment Utility
What kind of field data, what columns, too broad a question.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
You can use MySQL LIKE matching.  If you wanted to delete any row that had the word "Ray" in the title column, your query would say something pretty close to this:

DELETE FROM myTable WHERE title LIKE '% Ray %';

Note the blanks inside the percent signs around Ray. These are present to prevent the command from deleting a row that contained something like Raygun.  There is a considerable amount of "fine-tuning" needed to get MySQL LIKE clauses just right.  Sometimes a regular expression is better than a LIKE.

If you want to see how professionals approach a problem like this, have a go at Test-Driven Development. The technologies have matured a lot since I first wrote the article, but the principles are still mostly the same.
0
 

Author Comment

by:twittoris
Comment Utility
Can I do something like
DELETE FROM mytable WHERE mytable(title) = mytable(title); ?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I would need to see your test data structure and contents to be able to answer the question.  SQL has some fairly tight rules about how to write queries, and while I think you may be able to do some kind of automated deletion, the example showing mytable(title) in two places would not seem to make sense.
0
 

Author Comment

by:twittoris
Comment Utility
This is the table structure attached.
I just want to delete the row if there are duplicates in the title.
tablestructure.pdf
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
Comment Utility
DELETE t1 FROM news t1, news t2 WHERE t1.id > t2.id AND t1.title = t2.title

Open in new window

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:twittoris
Comment Utility
What does t1 and t2 represent? title or table?
This is making more sense to me on how to accomplish this task.
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
It's an alias for the table.

Delete from the table where the title is repeated and the row ID is greater so you are left with a unique row that has the lowest ID.
0
 

Author Comment

by:twittoris
Comment Utility
DELETE news FROM news t1, news t2 WHERE news.news.post_id > news.news.post_id AND news.news.title = news.news.title;

Its giving me an error
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
You might consider trying it like Gary wrote it.  The use of multiple dots in the SQL statement tells SQL to look for other databases.

Also, it's helpful if you show us the exact text of the error message!  You can usually copy/paste to be sure that we can see the same error message that you're seeing.
0
 

Author Comment

by:twittoris
Comment Utility
I got it Thanks Gary.
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
news.news.post_id > news.news.post_id
You can't cross reference the same table on the same column, that's why I alias the table with t1 and t2, even though it is the same table they are seen as two tables.
And as Ray pointed out that is looking for a database called news and table called news
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
htacces issue 2 37
How can I do this in Pyhton? 12 65
resizeing PHP image 2 20
Why does my array not "dump?" 5 4
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now