Deleting duplicates by title field

How do I delete entries that have any matching field data?
twittorisAsked:
Who is Participating?
 
GaryConnect With a Mentor Commented:
DELETE t1 FROM news t1, news t2 WHERE t1.id > t2.id AND t1.title = t2.title

Open in new window

0
 
GaryCommented:
What kind of field data, what columns, too broad a question.
0
 
Ray PaseurCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
twittorisAuthor Commented:
Can I do something like
DELETE FROM mytable WHERE mytable(title) = mytable(title); ?
0
 
Ray PaseurCommented:
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
 
twittorisAuthor Commented:
This is the table structure attached.
I just want to delete the row if there are duplicates in the title.
tablestructure.pdf
0
 
twittorisAuthor Commented:
What does t1 and t2 represent? title or table?
This is making more sense to me on how to accomplish this task.
0
 
GaryCommented:
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
 
twittorisAuthor Commented:
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
 
Ray PaseurCommented:
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
 
twittorisAuthor Commented:
I got it Thanks Gary.
0
 
GaryCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.