How do I write an update query for duplicate records

Question

My knowledge of MySQL is pretty basic and I struggle with complicated queries - for me, the following problem is complicated - hopefully it's not for you!

I've been importing businesses to my database, some of which have the same company name. For instance, we have two Subway branches in the table, both with different addresses.

I'd like to set the Company Name to be "Company Name" + "Town" so we end up with unique fields:

Subway London
Subway Manchester

So far, all I've managed to do is check out which Company names appear in the db twice with:

SELECT CompanyName , count( CompanyName )
FROM directorydata
GROUP BY CompanyName 
HAVING count( CompanyName ) >1

How do I go about updating each of the CompanyName field with it's current value, plus that of the Town field?

Your help is greatly appreciated!

score 0 · Answer 1 · edited May 23 '17 at 11:58

UPDATE directoryData 
   SET CompanyName = CONCAT(CONCAT(CompanyName, ' '), Town)
 WHERE CompanyName in (
                    SELECT CompanyName 
                      FROM directoryData
                      GROUP BY CompanyName
                      HAVING count(CompanyName)>1
                      )

EDIT: you are right, according to MySQL #1093 - You can't specify target table 'giveaways' for update in FROM clause , the engine could break if the updates change the value checked in the where (you update the first Subways record, then the second does not match the count()>1 clause anymore...)

Then, let's try another way:

UPDATE directoryData 
   SET CompanyName = (
         CASE WHEN (
                    SELECT count(CompanyName) 
                      FROM directoryData
                      GROUP BY CompanyName
                      HAVING count(CompanyName)
                   )
              >1
              THEN CONCAT(CONCAT(CompanyName, ' '), Town)
              ELSE CompanyName
              END
         )

I can't try it, i wrote this here by hand, hope that helps...

EDIT 2:

Using two tables for a one-time task (because previous solutions does not work on MySql):

UPDATE directoryData 
   SET CompanyName = CONCAT(CONCAT(CompanyName, ' '), Town)
 WHERE CompanyName in (
                    SELECT CompanyName 
                      FROM directoryData_CLONED
                      GROUP BY CompanyName
                      HAVING count(CompanyName)>1
                      )

How do I write an update query for duplicate records

1 Answers1