Updating specific MySQL table entries

Question

I am dealing with a telephone system and must work with several service providers. For one provider, I have a country_codes MySQL table, like this -

 --------------------------------------------------------- country_code | area_code | country --------------------------------------------------------- 93 | 93 | Afghanistan 0 | 9375 | Afghanistan Cellular-AT 0 | 9370 | Afghanistan Cellular-AWCC 355 | 355 | Albania 0 | 35568 | Albania Cellular-AMC 0 | 35567 | Albania Cellular-Eagle 213 | 213 | Algeria 0 | 21377 | Algeria Cellular-Djezzy 0 | 2135 | Algeria Cellular-Wataniya --------------------------------------------------------- 

etc.

The country_code column has not been there before, but I added it since I need it for my PHP application. I managed to update the country codes for some entries (using the answer from my previous question here)

I want to make sure to replace 0 with the corresponding country code. Therefore, the table should look like this:

 --------------------------------------------------------- country_code | area_code | country --------------------------------------------------------- 93 | 93 | Afghanistan 93 | 9375 | Afghanistan Cellular-AT 93 | 9370 | Afghanistan Cellular-AWCC 355 | 355 | Albania 355 | 35568 | Albania Cellular-AMC 355 | 35567 | Albania Cellular-Eagle 213 | 213 | Algeria 213 | 21377 | Algeria Cellular-Djezzy 213 | 2135 | Algeria Cellular-Wataniya --------------------------------------------------------- 

I hope I explained myself well enough. Any idea how I can do this with PHP-MySQL?

(I don't mind using PHP code to manipulate a table this way)

Answer 1

Try this query -

 UPDATE country_codes SET country_code := @c := IF(@c IS NOT NULL AND country_code = 0, @c, country_code) ORDER BY CAST(area_code AS CHAR) 

Answer 2

 update cc set country_code = t.country_code from country_codes cc join ( select country_code, country, char_length(trim(cast(country_code as char))) as code_len from country_codes where country_code <> 0 ) t on t.country_code = cast(substr(cast(cc.area_code as char), 1, t.code_len) as signed integer) and cc.country_code = 0 and cc.country like concat(t.country, '%') 

I added cc.country like concat(t.country, '%') to the condition to be more specific, but assumes that each name of the cellular network starts with its own country name, so if it is not, omit it.

Added after comment by @Sachyn:

The test code used in SQLZOO works fine, it is intended only for testing, this is not an update request

 select cc.*, t.country_code as new_country_code from ( select 93 as country_code, 93 as area_code , 'Afghanistan' as country union select 0 , 9375 , 'Afghanistan Cellular-AT' union select 0 , 9370 , 'Afghanistan Cellular-AWCC' union select 355, 355 , 'Albania' union select 0 , 35568, 'Albania Cellular-AMC' union select 0 , 35567, 'Albania Cellular-Eagle' union select 213, 213 , 'Algeria' union select 0 , 21377, 'Algeria Cellular-Djezzy' union select 0 , 2135 , 'Algeria Cellular-Wataniya' ) cc join ( select country_code, country, char_length(rtrim(cast(country_code as char))) as code_len from ( select 93 as country_code, 93 as area_code , 'Afghanistan' as country union select 0 , 9375 , 'Afghanistan Cellular-AT' union select 0 , 9370 , 'Afghanistan Cellular-AWCC' union select 355, 355 , 'Albania' union select 0 , 35568, 'Albania Cellular-AMC' union select 0 , 35567, 'Albania Cellular-Eagle' union select 213, 213 , 'Algeria' union select 0 , 21377, 'Algeria Cellular-Djezzy' union select 0 , 2135 , 'Algeria Cellular-Wataniya' ) c where country_code <> 0 ) t on t.country_code = cast(substr(cast(cc.area_code as char), 1, t.code_len) as signed integer) and cc.country_code = 0 and cc.country like concat(t.country, '%') 

Answer 3

If you need to do data correction ONLY once, you can try this approach:

0) Backing up data or, better, starting a request to copy data.

1) Creates a table containing non-zero country codes. We need a separate table because the MySQL manual says that:

Currently, you cannot update the table and select from the same table in the subquery.

 CREATE TABLE country_codes_list ( country_code INT NOT NULL PRIMARY KEY ); INSERT INTO country_codes_list SELECT country_code FROM country_codes WHERE country_code <> 0; 

2) Update all the lines where the country code is 0 by searching for the country code that matches the beginning of the area code:

 UPDATE country_codes AS country_codes_zero SET country_code = ( SELECT country_code FROM country_codes_list WHERE country_codes_list.country_code = SUBSTRING(country_codes_zero.area_code, 1, LENGTH(country_codes_list.country_code)) ) WHERE country_code = 0; 

This can be a very slow query since it uses the subquery associated with it. But he has to capture the data at a time.

Answer 4

Something like this should work, assuming you have a database called "testdb" and a table called "footable".

 $link = mysql_connect("localhost", "username", "password1234"); mysql_select_db("testdb", $link); $result = mysql_query("UPDATE footable SET country_code="93" WHERE country LIKE Afghanistan%", $link); $result = mysql_query("UPDATE footable SET country_code="355" WHERE country LIKE Albania%", $link); mysql_close($link); 

Answer 5

You can only create a table with countries , that is:

 INSERT INTO countries (country_code,country) SELECT country_code,country FROM country_codes WHERE country_code=0 

Then you can easily update the table

 UPDATE country_codes cc, countries c SET cc.country_code = c.country_code WHERE LOCATE(c.country,cc.country)=1 AND cc.country_code=0 

I have not tested this, but I think you can understand.