CODEWITHSUNDEEP
mysql
user1051897
user1051897

Reputation:

Concatenate 2 columns given a condition

I am having following scenario: I have a table lets say X where fields are

 -------------------------
 | City      | Country   |
 ------------------------|
 | Melbourne | Australia |
 | Phoenix   | USA       |
 | Manchester| USA       |
 | Manchester| UK        |
 | Phoenix   | USA       |
 | Pune      | India     |
 ------------------------

I am trying to update city column only when city are same and country are different. I have tried doing

UPDATE X SET (City) = CONCAT(CITY,COUNTRY) WHERE = ?

What should be where clause?

EDIT:

Output should look like

 ------------------------------
 | City           | Country   |
 -----------------------------|
 | Melbourne      | Australia |
 | Phoenix        | USA       |
 | Manchester, USA| USA       |
 | Manchester, UK | UK        |
 | Phoenix        | USA       |
 | Pune           | India     |
 ------------------------------

Upvotes: 2

Views: 236

Answers (4)

fthiella
fthiella

Reputation: 49049

UPDATE X inner join X X_1 on X.city = X_1.city
SET X.City=CONCAT(X.city, ', ', X.Country)
WHERE X.country <> X_1.country

this sets City name to CONCAT(X.city, ', ', X.Country) only where the same City name exists in multiple countries.

EDIT: i think i prefer this other version:

UPDATE
  X inner join
  (select city from X group by city having count(*)>1) X_dup
  on X.city = X_dup.city
SET X.City=CONCAT(X.city, ', ', X.Country)

I'm using all these strange joins because MySql doesn't allow you to update a table if you are referencing it in a subquery.

Upvotes: 1

Avin Varghese
Avin Varghese

Reputation: 4370

Try using :

GROUP_CONCAT()

Returns a string result with the concatenated non-NULL values from a group

http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/

Upvotes: 0

Vyktor
Vyktor

Reputation: 20997

You can get City of all duplicate towns by query like this:

SELECT City, COUNT(City) AS Count
FROM X
GROUP BY City
HAVING Count > 1

It would be easiest to use IN in condition (only one column), so will encapsulate this:

SELECT City
FROM (
    SELECT City, COUNT(City) AS Count
    FROM X
    GROUP BY City
    HAVING Count > 1
) AS t1

Now we have to push all data into temporary table because mysql doesn't work when you try using nested table select while updating the same table:

CREATE TEMPORARY TABLE tmp (VARCHAR(255) city);
INSERT INTO tmp (
    SELECT City
    FROM (
        SELECT City, COUNT(City) AS Count
        FROM X
        GROUP BY City
        HAVING Count > 1
    ) AS t1
)

And now we may launch update:

UPDATE X SET (City) = CONCAT(CITY,COUNTRY)
WHERE `City` IN (SELECT City FROM tmp);

Upvotes: 0

aykut
aykut

Reputation: 3094

UPDATE X SET (City) = CONCAT(CITY,', ',COUNTRY) 
WHERE CITY IN 
    ( 
    SELECT tmp.City FROM 
        (SELECT DISTINCT x2.City, x2.Country FROM X AS x2) AS tmp 
    GROUP BY tmp.City HAVING COUNT(1) > 1 
    )

Upvotes: 1

Related Questions