Help with opposite of DISTINCT in mysql

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

George-TCCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

George-TCCAuthor Commented:
bump - any ideas?
0
nasirbestCommented:
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
theGhost_k8Database ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

theGhost_k8Database ConsultantCommented:
Sorry, make it TRUE.
That will make all the first records of duplicate as true.
0
DangItMarilynCommented:
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
George-TCCAuthor Commented:
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
DangItMarilynCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.