Solved

Deleting duplicates by title field

Posted on 2014-12-17
12
60 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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 …

730 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