Menu
I am dealing with phone system and have to work with multiple service vendors. For one vendor I have a MySQL table country_codes 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
---------------------------------------------------------
and so on...
The country_code column wasn't there before but I added it since I needed it for my PHP application. I managed to update the country codes for some records (using answer from my previous question here)
What I want to achieve is to replace the 0's with the respective country code. So 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 can I do that with PHP-MySQL ?
(I don't mind using PHP code to manipulate the table this way)
533
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.
The UPDATE command in SQL is used to modify or change the existing records in a table. If we want to update a particular value, we use the WHERE clause along with the UPDATE clause. If you do not use the WHERE clause, all the rows will be affected.
There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);
The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement. UPDATE table_name SET column1 = value1, column2 = value2,...
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
