Generate city list for UPDATE statement in MySQL

Generate city list for UPDATE statement in MySQL

Scenario:

We have tool_ip table holds visitor ip log with city and country information, now we have a requirement to generate an DML to update country information in another table. This is not a regular transformation into DML for sure, due to multiple values across rows in the source table.

We use the following script to discover the intermediate result – city list.

SELECT count(DISTINCT city) citycount, 
LOCATE('\',\'',GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'')) pos,
GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'') cities
FROM tool_ip
where trim(country) !='()' and trim(city)!=''
group by country
HAVING GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'') != ''

Query result look like this:

Now we can get the final sql script:

SET SESSION group_concat_max_len = 1000000;
SELECT concat(
    'update wp_activity_log set country=\'', 
     SUBSTRING_INDEX(SUBSTRING_INDEX(country, '(', -1), ')', 1),
     '\' where city in (\'', GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\''), '\');'
) `sql`
FROM tool_ip
WHERE trim(country) !='()' and trim(city)!=''
GROUP BY country
HAVING GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'') != ''

Result:

Key points gained:

  1. Country format is like ‘Canada (CA)’ in database, so to get ISO country code like this:
    SUBSTRING_INDEX(SUBSTRING_INDEX(country, '(', -1), ')', 1)
  2. data contains empty city and country that we need to filter out in order to reduce interference factors;
  3. Using GROUP_CONCAT() function to combine cities into one string in order to build update statement in SQL.
  4. In order to overcome the string length limit of GROUP_CONCAT() function add SET SESSION group_concat_max_len = 1000000; on top of the script.

Done.

243 total views, 3 views today

Author: Albert

Leave a Reply