update rows where they are repeated in a column?

Question

I'm working with a pretty large database of cities. I have several rows of cities that are repeated because they are from different countries / states.

What I need to do is: The cities that are repeated, concatenate in this way (ascii_name + "_"+ country_code) in column: name_city

Only the cities that are repeated.

with this filter code those that are repeated:

SELECT ascii_name FROM cities GROUP BY ascii_name HAVING count (ascii_name)> 1

For example(img) with "Rome" I get 3 rows and I want to differentiate them in that column "name_city" with "country_code"

(It's mysql)

Ravishankar Sivasubramaniam · Answer 1 · 2019-04-15T23:44:45.970

0

Not sure if this is what you are looking.

Concatenation syntax differs based on DB. || works in ANSI concatenation operator and it works in Oracle, + works in SQL server, CONCAT function works in MySQL.

SELECT CONCAT(ascii_name,'_',country_code) as name_city from cities 
where ascii_name in (select ascii_name FROM cities GROUP BY ascii_name HAVING count (ascii_name)> 1)

edited Apr 15 '19 at 23:44

answered Apr 15 '19 at 23:39

Ravishankar Sivasubramaniam

771
5
9

if it filters me well to what I want. I want that same thing that I update the column name_city of the whole table of those that are duplicated. (It's mysql) – manolo manolin Apr 15 '19 at 23:50
@manolomanolin Sorry I don't get it. The above query will give you all the cities that are repeated. Do you also want the cities that are not repeated? without the country code concatenated – Ravishankar Sivasubramaniam Apr 15 '19 at 23:53
I want to update the name_city column of that select – manolo manolin Apr 15 '19 at 23:55
```UPDATE cities set name_city = CONCAT(ascii_name,'_',country_code) ```will update all rows – Ravishankar Sivasubramaniam Apr 15 '19 at 23:56
If you want to update only the cities that are repeated ```UPDATE cities set name_city = CONCAT(ascii_name,'_',country_code) where ascii_name in (select ascii_name FROM cities GROUP BY ascii_name HAVING count (ascii_name)> 1)``` – Ravishankar Sivasubramaniam Apr 15 '19 at 23:57
#1093 - You can't specify target table 'cities' for update in FROM clause =( – manolo manolin Apr 16 '19 at 00:03
I would create a temporary table with all the cities that are repeated and use that in the filter. ```CREATE TABLE rep_cities as SELECT ascii_name FROM cities ascii_name in (select ascii_name FROM cities GROUP BY ascii_name HAVING count (ascii_name)> 1);``` then run update ```UPDATE cities set name_city = CONCAT(ascii_name,'_',country_code) where ascii_name in (select ascii_name FROM rep_cities)``` – Ravishankar Sivasubramaniam Apr 16 '19 at 00:09
@manolomanolin See https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause for how to fix that error. – Barmar Apr 16 '19 at 01:23

score 0 · Accepted Answer · answered Apr 15 '19 at 23:57

0

Use an UPDATE query that joins with a subquery that finds all the duplicated names.

UPDATE cities AS c1
JOIN (SELECT ascii_name
      FROM cities
      GROUP BY ascii_name
      HAVING COUNT(*) > 1) AS c2
    ON c1.ascii_name = c2.ascii_name
SET c1.name_city = CONCAT(c1.ascii_name,'_',c1.country_code)

answered Apr 15 '19 at 23:57

Barmar

741,623
53
500
612

#1052 - La columna: 'ascii_name' en field list es ambigua – manolo manolin Apr 16 '19 at 00:04
Did you copy my original code before I edited `ascii_name` to `c1.ascii_name`? – Barmar Apr 16 '19 at 00:18
thx this code was correct, I managed to do what I wanted. =DDD – manolo manolin Apr 16 '19 at 01:48

score -1 · Answer 3 · answered Apr 15 '19 at 23:37

-1

I think your subquery should run fine

select ascii_name+"_"+country_code as name_city
from cities
where ascii_name in
(SELECT ascii_name FROM cities GROUP BY ascii_name HAVING count (ascii_name)> 1);

answered Apr 15 '19 at 23:37

Rhythm

680
5
9

1

+ operator does not work in MySQL for concatenation – Ravishankar Sivasubramaniam Apr 15 '19 at 23:48
@Ravishankar Sivasubramaniam , I wrote pseudo code by copying what he mentioned to give him an idea how the query will look like..Didn't execute it – Rhythm Apr 16 '19 at 15:24

update rows where they are repeated in a column?

3 Answers3