更新MySQL表的特定记录

Question

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

---------------------------------------------------------
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应用程序。我设法更新了一些记录的国家代码(使用了前面问题的答案)

我想要实现的是用相应的国家代码替换0。所以桌子应该是这样的-

---------------------------------------------------------
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代码这样操作表)

Answer 1

试试这个查询-

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, '%')

我已经将cc.country like concat(t.country, '%')添加到更具体的条件中，但它假设每个蜂窝网络名称都以其国家名称开头--所以如果它不是真的，就省略它。

在@Sachyn评论之后添加了：

在SQLZOO上使用的测试代码很好，只用于测试，它不是一个更新查询

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

如果您只需要修复数据一次，可以尝试以下方法：

0)备份数据，或者更好的是，在数据副本上运行查询。

1)创建一个包含非零国家代码的表。我们需要单独的表，因为它在MySQL手册中说：

当前，无法更新表并从子查询中的同一表中进行选择。

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)通过查找与区号开头匹配的国家代码，更新国家代码为0的所有行：

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;

这可能是一个非常慢的查询，因为它使用了一个与协同相关的子查询。但它应该一次修复数据。