myyis
asked on
manipulate duplicate data
My DOCUMENT table has duplicate entries when I group BY name, size
The result of this query is
SELECT name, COUNT( * ) , id,filename FROM DOCUMENT GROUP BY name, size HAVING COUNT( * ) >1
name COUNT(*) id filename
--------------------------
docu1 45 33 fname1
docu2 85 59 fname2
docu3 43 33 fname5
I want to change the "filename" of all recurring entries to the filename of the first entry of the group. (That means the first record of the group will be unchanged)
Can any body help me,
Thank you so much
The result of this query is
SELECT name, COUNT( * ) , id,filename FROM DOCUMENT GROUP BY name, size HAVING COUNT( * ) >1
name COUNT(*) id filename
--------------------------
docu1 45 33 fname1
docu2 85 59 fname2
docu3 43 33 fname5
I want to change the "filename" of all recurring entries to the filename of the first entry of the group. (That means the first record of the group will be unchanged)
Can any body help me,
Thank you so much
Gary
So where for example the ID is 33 all entries should be filename1 (not filename5 etc)?
ASKER
Like this
name size id filename
--------------------------
docu1 10 33 fname1
docu1 10 34 fname4 change to fname1
docu1 10 35 fname6 change to fname1
docu2 20 36 fname7
docu2 20 36 fname8 change to fname7
Also I need to have the list of the old (changed) "filename" values ("fname4","fname6","fname8
Thank you
name size id filename
--------------------------
docu1 10 33 fname1
docu1 10 34 fname4 change to fname1
docu1 10 35 fname6 change to fname1
docu2 20 36 fname7
docu2 20 36 fname8 change to fname7
Also I need to have the list of the old (changed) "filename" values ("fname4","fname6","fname8
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
if possible using SELECT somewhere
Yes, but to do what with it?
But you cannot do a recordset select while doing an update - it's one or the other
But you cannot do a recordset select while doing an update - it's one or the other
ASKER
I will use the list to delete the repeating documents
ASKER
I mean delete from directory. So may be I can use a SELECT first, then the 2d query to change
Delete from what directory?
This question is in the MySQL zone.
This question is in the MySQL zone.
ASKER
Yeah I know,
if you can provide me also the SELECT query, I can use the result set of records to will be changed.
Thank you.
if you can provide me also the SELECT query, I can use the result set of records to will be changed.
Thank you.
You can use this which will give a comma seperated field called dupes which contains all the grouped filenames
SELECT GROUP_CONCAT(filename) AS dupes FROM table GROUP BY nameASKER
Thank you for the select but also it gives the results that are unique.
I need something like this ("fname4","fname6","fname8
I need something like this ("fname4","fname6","fname8
Is ID a unique auto increment field?
ASKER
No, the PK is (ID,ORID)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great!