更新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屋!