Welcome to WebmasterWorld Guest from 107.21.175.43

Forum Moderators: open

Message Too Old, No Replies

Mysql Command for Duplicates

Mysql Command for Duplicates

     

rajivadhy

8:00 am on Jul 22, 2010 (gmt 0)

5+ Year Member



Hi,

Need help filtering duplicates from a category

fname- catid ( id of category)
fname- company_name ( name of company)

For catid () find all duplicate company_name

Some categories eg. have the same company name 5 times

What will be the sql command in phpmyadmin

Regards

brotherhood of LAN

8:20 am on Jul 22, 2010 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Using Group By would return only unique company names.


SELECT *
FROM table
WHERE ...
GROUP BY company_name

rajivadhy

9:06 am on Jul 22, 2010 (gmt 0)

5+ Year Member



sorry how would the full query be ?

brotherhood of LAN

9:13 am on Jul 22, 2010 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



If you post the query you currently have, then I'd be able to add to it as I don't know the table names or what you want to do with the query.

That said, if you try putting the GROUP BY statement after your WHERE statement, it should work fine.

rajivadhy

9:53 am on Jul 22, 2010 (gmt 0)

5+ Year Member



I dont have a queury
Need to
UPDATE table_product and remove duplicate company_name ( name of company)
from cat_id (category)

brotherhood of LAN

10:48 am on Jul 22, 2010 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



I'm not quite sure what you want to do, hopefully you can elaborate a bit more or someone else can chip in. Sorry I couldn't help out!

rocknbil

5:52 pm on Jul 22, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Sometimes the universe just . . . aligns. :-) I have a project where just last evening, the **exact** same issue came up. Unfortunately, I haven't found a way to do it in one statement, but I *have* figured out a way to correctly count it and use that as a trigger to execute a nested command in programming, which is at least a *little* more efficient than searching the entire table.

Start with how to determine the duplicates. Working example:

create table test2 (id int(11) primary key auto_increment, some_val varchar(255));

insert into test2 (some_val) values ('abcd');
insert into test2 (some_val) values ('abcde');
insert into test2 (some_val) values ('abcd');
insert into test2 (some_val) values ('abcde');
insert into test2 (some_val) values ('abcd');
insert into test2 (some_val) values ('abcdfg');
insert into test2 (some_val) values ('abcdhi');
insert into test2 (some_val) values ('abcdjk');

then

select count(*) as num, id,some_val from test2 group by some_val having count(*) > 1;

-->

num | id | some_val
3 | 1 | abcd
2 | 2 | abcde

Note it reports the first instance of the duplicate for the id. so using this, I know that records with id = 1 or id = 2 have duplicates, I can then execute like this:


$query = "select count(*) as num, id,some_val from test2 group by some_val having count(*) > 1";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$query = "update test2 set some_val = concat(some_val,'-',id) where some_val='" .
$row[2] . "' and id <> " . $row[1];
mysql_query($query);
}


The above code is untested, make sure you don't try this on live data. But I think it will work . . . maybe someone can extract a single nested query from the groundwork here, that would sure make life easier . . . .
 

Featured Threads

Hot Threads This Week

Hot Threads This Month