如何将同表相同id的非空url值更新至同id不同lang的空url字段

核心逻辑 #

按id字段分组，提取每个分组下非空的url值，将其回写到同id下url为空的行中。
以下以表名为t_content为例，可根据实际表名替换：

MySQL 写法 #

UPDATE t_content t1
JOIN (
    -- 提取每个id对应的非空url
    SELECT id, url 
    FROM t_content 
    WHERE url IS NOT NULL AND url != ''
) t2 ON t1.id = t2.id
-- 仅更新url为空的行
SET t1.url = t2.url
WHERE t1.url IS NULL OR t1.url = '';

PostgreSQL 写法 #

UPDATE t_content t1
SET url = t2.url
FROM (
    SELECT id, url 
    FROM t_content 
    WHERE url IS NOT NULL AND url != ''
) t2
WHERE t1.id = t2.id
AND (t1.url IS NULL OR t1.url = '');

SQL Server 写法 #

UPDATE t1
SET t1.url = t2.url
FROM t_content t1
INNER JOIN (
    SELECT id, url 
    FROM t_content 
    WHERE url IS NOT NULL AND url != ''
) t2 ON t1.id = t2.id
WHERE t1.url IS NULL OR t1.url = '';

注意事项 #

如果单个id下存在多个非空url的场景，需要先调整子查询逻辑，指定取哪一个url值，避免匹配重复报错。可参考修改子查询为以下形式，使用聚合函数固定取值：

SELECT id, MAX(url) as url 
FROM t_content 
WHERE url IS NOT NULL AND url != '' 
GROUP BY id

内容的提问来源于stack exchange，提问作者Laimonas