SQL查询：通过用户名关联将Table2两列值合并填充Table1的real_name列

需求是通过Table1.audit_username与Table2.username关联，把Table2的first_name和second_name拼接成完整姓名后，更新Table1.real_name字段。以下是不同数据库环境下的实现语句：

MySQL/MariaDB #

使用CONCAT函数拼接字符串，通过JOIN关联两张表进行更新：

UPDATE Table1 t1
JOIN Table2 t2 ON t1.audit_username = t2.username
SET t1.real_name = CONCAT(t2.first_name, ' ', t2.second_name);

如果要避免空值导致的多余空格，可改用CONCAT_WS（自动跳过空值）：

UPDATE Table1 t1
JOIN Table2 t2 ON t1.audit_username = t2.username
SET t1.real_name = CONCAT_WS(' ', t2.first_name, t2.second_name);

SQL Server #

通过INNER JOIN关联后直接更新，用+拼接字符串：

UPDATE t1
SET t1.real_name = t2.first_name + ' ' + t2.second_name
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.audit_username = t2.username;

若需处理空值，可结合ISNULL：

UPDATE t1
SET t1.real_name = ISNULL(t2.first_name, '') + ' ' + ISNULL(t2.second_name, '')
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.audit_username = t2.username;

Oracle #

使用子查询关联更新，用||拼接字符串，同时用WHERE EXISTS避免无匹配数据的行被更新为NULL：

UPDATE Table1 t1
SET real_name = (SELECT t2.first_name || ' ' || t2.second_name
                 FROM Table2 t2
                 WHERE t2.username = t1.audit_username)
WHERE EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.username = t1.audit_username);

处理空值可使用NVL：

UPDATE Table1 t1
SET real_name = (SELECT NVL(t2.first_name, '') || ' ' || NVL(t2.second_name, '')
                 FROM Table2 t2
                 WHERE t2.username = t1.audit_username)
WHERE EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.username = t1.audit_username);

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