homepage Welcome to WebmasterWorld Guest from 54.237.95.6
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Mysql Command for Duplicates
Mysql Command for Duplicates
rajivadhy




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

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




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

Using Group By would return only unique company names.


SELECT *
FROM table
WHERE ...
GROUP BY company_name

rajivadhy




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

sorry how would the full query be ?

brotherhood of LAN




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

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




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

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




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

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




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

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 . . . .

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved