如何通过依赖表字段的子查询按行更新emails表的email字段

现有emails表数据 #

正确SQL写法（PostgreSQL语法，适配原语句的类型转换规则） #

不需要嵌套子查询，直接通过UPDATE ... FROM关联所需表即可，外层emails表的字段可以直接在SET表达式中调用：

UPDATE emails
SET email = CONCAT(
    LOWER(users.first_name),
    '.',
    LOWER(users.last_name),
    '.',
    users.id::TEXT,
    '@',
    emails.tenant_id::TEXT,
    '.',
    users.language::TEXT,
    '.com'
)
FROM users
INNER JOIN tenants ON users.tenant_id = tenants.id
WHERE 
    -- 保证每行email记录匹配对应用户
    emails.user_id = users.id
    -- 保证租户匹配，避免同用户多租户场景匹配错误
    AND emails.tenant_id = tenants.id;

必须使用子查询的兼容写法 #

如果场景要求必须用子查询实现，需要在子查询中同时返回user_id和tenant_id作为关联条件，避免匹配错误：

UPDATE emails
SET email = Subquery.userEmails
FROM (
    SELECT 
        CONCAT(
            LOWER(u.first_name),
            '.',
            LOWER(u.last_name),
            '.',
            u.id::TEXT,
            '@',
            e.tenant_id::TEXT,
            '.',
            u.language::TEXT,
            '.com'
        ) AS userEmails,
        e.user_id,
        e.tenant_id
    FROM emails e
    INNER JOIN users u ON e.user_id = u.id
    INNER JOIN tenants t ON e.tenant_id = t.id
) Subquery
WHERE 
    emails.user_id = Subquery.user_id 
    AND emails.tenant_id = Subquery.tenant_id;

原写法问题说明 #

1. 第一版SQL没有添加外层emails表和子查询的关联条件，数据库无法建立子查询行和待更新行的对应关系，会触发笛卡尔积，所有email行都会被更新为同一个错误值。
2. 第二版SQL仅用user_id作为关联条件，若存在同一个user_id归属不同tenant_id的记录，会出现匹配错误，且嵌套子查询属于冗余写法，执行性能低于直接关联的方案。

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