?
Solved

Help with opposite of DISTINCT in mysql

Posted on 2010-04-05
7
Medium Priority
?
424 Views
Last Modified: 2013-12-12
Hi There,

I have a problem where I have a database table with duplicate records in it.
What I want to do is change the field "searchableFoodItem" to false if it is a duplicate record.

Please note the count on some duplicates is 19 or more.

I want to always keep the first record of the duplicates set as true but every other duplicate I want as false.

Here is my query which I am using which shows the counts.

Any help would be appreciated.

Thanks in advance,
George
select item, carbohydrate, quantityServe, protein, fat, searchableFoodItem, count(*) as count
from myTable
group by item
having count > 1

Open in new window

0
Comment
Question by:George-TCC
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Author Comment

by:George-TCC
ID: 29860335
bump - any ideas?
0
 
LVL 6

Expert Comment

by:nasirbest
ID: 29874046
With a variable you can compare food item if matched then false. if do not. save food item value into this variable so next time if same food item repeat compare will be found and function return false. and remember to sort query by food item otherwise it will not work as excepted.



SET @food_item:=0;

SELECT item, 
            carbohydrate, 
            quantityServe, 
            protein, 
            fat, 
            IF(searchableFoodItem=@food_item, false, @food_item:=searchableFoodItem) AS searchableFoodItem, 
            count(*) as count
FROM myTable
ORDER BY searchableFoodItem

Open in new window

0
 
LVL 21

Accepted Solution

by:
K V earned 2000 total points
ID: 29875577
This should do:
update TABLE1, (select id from TABLE1 group by DUPLICATECOLUMN having count(*)>1) x  set TABLE1.FIELD=FALSE where x.id=TABLE1.id;
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 21

Expert Comment

by:K V
ID: 29875661
Sorry, make it TRUE.
That will make all the first records of duplicate as true.
0
 
LVL 2

Expert Comment

by:DangItMarilyn
ID: 31327589
You asked for more answers, but you haven't replied to the other answers here. Did you attempt them? Did they fail and in what way?

0
 

Author Comment

by:George-TCC
ID: 31328278
Yes I tried those answers but found my solution by my own trial and error over the days and resolved it myself. The way the failed was but not displaying the results at all in my query
0
 
LVL 2

Expert Comment

by:DangItMarilyn
ID: 31332455
Ok, great! Glad you got it figured out. You should close the question by choosing an answer or request it closed :)

It's popping up on the "omg answer me now!!!" alerts
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

593 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