Rename duplicate record with PHP

Question

I know you can select duplicate rows in MYSQL with the following query:

SELECT ID, bedrijfsnaam, plaats, COUNT( * ) 
FROM profiles
GROUP BY bedrijfsnaam, plaats
HAVING COUNT( * ) >1

I want to able to select the duplicates and rename them with PHP. Is that possible? If it finds three duplicates for one 'bedrijfsnaam-plaats'-combination, I want to add 'I', 'II', 'III' at the end of the 'bedrijfsnaam' value. Is that even possible? Is there some kind of loop I could run?

Thanks..

Yes. It's possible - although the rules for roman numerals are a little cumbersome — Strawberry, Nov 22 '13 at 21:37
In software the answer to almost all 'is it possible' questions is 'yes'. This is no exception. The real question is 'how much does it cost?' Try something, post the code if you get stuck — , Nov 22 '13 at 21:37

Denis de Bernardy · Answer 1 · 2013-11-22T22:05:54.177

1

Instead of counting, you can find the rows that are dups directly:

SELECT DISTINCT p2.ID, p2.bedrijfsnaam, p2.plaats
FROM profiles p
JOIN profiles p2 on p2.bedrijfsnaam = p.bedrijfsnaam
 AND p2.plaats = p.plaats
 AND p2.ID > p.ID          -- this is where the magic occurs

Then run an update accordingly...

edited Nov 22 '13 at 22:05

answered Nov 22 '13 at 21:37

Denis de Bernardy

75,850
13
131
154

Perhaps, but this isn't an answer to the question. – Nov 22 '13 at 21:39
@MikeW: well, hopefully OP can take it from there and run an update... – Denis de Bernardy Nov 22 '13 at 21:39
Probably not, since the essence of his question was 'how do I perform an update?' – Nov 22 '13 at 21:41
1

I see no reason to downvote, even though the answer was obvious, selecting them directly is the start of the solution indeed. Thanks Denis. – user2704687 Nov 22 '13 at 21:43
This query can give strange results for higher level multiplications... If there are 3 same record, the highest id's owner will be listed twice. – Lajos Veres Nov 22 '13 at 21:57

Lajos Veres · Accepted Answer · 2013-11-22T22:07:40.620

1

I think these 2 query can be an answer:

create table maptable as 
SELECT
  p2.ID,
  concat(p2.bedrijfsnaam,count(*)) as bedrijfsnaam_new
FROM profiles p
JOIN profiles p2 on p2.bedrijfsnaam = p.bedrijfsnaam
 AND p2.plaats = p.plaats
 AND p2.ID > p.ID
group by p2.id,p2.bedrijfsnaam, p2.plaats;

update
  profiles,
  maptable
set
  profiles.bedrijfsnaam=bedrijfsnaam_new
where
  profiles.id=maptable.id;

The first part is based on Denis' answer...

edited Nov 22 '13 at 22:07

answered Nov 22 '13 at 22:02

Lajos Veres

13,595
7
43
56

I think OP wants roman numerals. :-P – Denis de Bernardy Nov 22 '13 at 22:09
:-) For small numbers it is possible to use `repeat('I',count(*))` instead of the `count(*)`... But this gives correct roman number only under four... – Lajos Veres Nov 22 '13 at 22:12
Yeyeyeye. We can do that. :-) – Denis de Bernardy Nov 22 '13 at 22:28
(Btw, shoot me an email if you're in Budapest. I'm in Hungary too.) – Denis de Bernardy Nov 22 '13 at 22:31
That looks like it should do it, thanks a lot! – user2704687 Nov 25 '13 at 21:32

Rename duplicate record with PHP

2 Answers2