Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

manipulate duplicate data

Posted on 2014-11-22
15
Medium Priority
?
141 Views
Last Modified: 2014-11-22
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
0
Comment
Question by:myyis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40459837
So where for example the ID is 33 all entries should be filename1 (not filename5 etc)?
0
 
LVL 1

Author Comment

by:myyis
ID: 40459844
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
0
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 40459861
Try
UPDATE table1 a
INNER JOIN
(SELECT filename,name FROM table1) b
ON a.name = b.name
SET a.filename=b.filename

Open in new window


list of the old (changed) "filename"
a list where?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Author Comment

by:myyis
ID: 40459872
if possible using SELECT somewhere
0
 
LVL 58

Expert Comment

by:Gary
ID: 40459877
Yes, but to do what with it?
But you cannot do a recordset select while doing an update - it's one or the other
0
 
LVL 1

Author Comment

by:myyis
ID: 40459880
I will use the list to delete the repeating documents
0
 
LVL 1

Author Comment

by:myyis
ID: 40459884
I mean delete from directory. So may be I can use a SELECT first, then the 2d query to change
0
 
LVL 58

Expert Comment

by:Gary
ID: 40459889
Delete from what directory?
This question is in the MySQL zone.
0
 
LVL 1

Author Comment

by:myyis
ID: 40459900
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40459915
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 name

Open in new window

0
 
LVL 1

Author Comment

by:myyis
ID: 40459980
Thank you for the select but also it gives the results that are unique.
I need something like this ("fname4","fname6","fname8"). Please check above
0
 
LVL 58

Expert Comment

by:Gary
ID: 40460000
Is ID a unique auto increment field?
0
 
LVL 1

Author Comment

by:myyis
ID: 40460007
No, the PK is (ID,ORID)
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 2000 total points
ID: 40460031
SELECT filename
FROM table1 a
WHERE filename NOT IN 
(SELECT filename FROM (select name,filename from table1 group by name) b) 

Open in new window

0
 
LVL 1

Author Closing Comment

by:myyis
ID: 40460036
Great!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

670 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