Solved

Deleting duplicates by title field

Posted on 2014-12-17
12
61 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 110

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 110

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 500 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 110

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

691 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