更新MySQL表的特定记录 [英] Update specific records of MySQL table

问题描述

我正在处理电话系统，因此必须与多家服务供应商合作.对于一个供应商，我有一个这样的MySQL表country_codes-

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
---------------------------------------------------------

以此类推...

country_code列以前不存在，但是我添加了它，因为我的PHP应用程序需要它.我设法更新了一些记录的国家代码(使用此处的上一个问题的答案)

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)

我要实现的是用相应的国家/地区代码替换0.所以表格应该看起来像这样-

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
---------------------------------------------------------

我希望我对自己的解释足够好.知道如何使用PHP-MySQL做到这一点吗?

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

(我不介意使用PHP代码以这种方式操作表)

推荐答案

尝试此查询-

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)

这篇关于更新MySQL表的特定记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！