?
Solved

Deleting duplicates by title field

Posted on 2014-12-17
12
Medium Priority
?
63 Views
Last Modified: 2014-12-25
How do I delete entries that have any matching field data?
0
Comment
Question by:twittoris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40505454
What kind of field data, what columns, too broad a question.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40506052
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
ID: 40509239
Can I do something like
DELETE FROM mytable WHERE mytable(title) = mytable(title); ?
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40510788
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
ID: 40513315
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 2000 total points
ID: 40517021
DELETE t1 FROM news t1, news t2 WHERE t1.id > t2.id AND t1.title = t2.title

Open in new window

0
 

Author Comment

by:twittoris
ID: 40517602
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
ID: 40517605
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
ID: 40517668
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 111

Expert Comment

by:Ray Paseur
ID: 40517675
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
ID: 40517676
I got it Thanks Gary.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40517680
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

770 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