如何基于唯一FirstName将SportsTable的Player字段更新为对应Email

问题背景 #

我们有两张表：

PlayerTable #

SportsTable #

需求：将SportsTable中Players列的玩家名字替换为对应邮箱，但仅更新那些在PlayerTable中FirstName唯一的记录（即Laura、Josh），重复名字的Bob、Alex保持原值不变。

错误分析 #

你之前的更新语句存在两个问题：

1. 子查询返回P.Email, P.FirstName两列，但SET Player = (...)只能接收单个字段值，导致语法错误。
2. 分组逻辑错误：GROUP BY Email, FirstName会把同一个FirstName但不同Email的记录分成不同组，无法正确统计FirstName的总出现次数，应该单独按FirstName分组统计总数。

正确的SQL更新语句 #

通用思路 #

先筛选出PlayerTable中唯一的FirstName及其对应的Email，再关联SportsTable进行定向更新。

MySQL/MariaDB

UPDATE SportsTable s
JOIN (
    -- 先获取唯一FirstName对应的Email
    SELECT p.Email, p.FirstName
    FROM PlayerTable p
    JOIN (
        -- 筛选出只出现一次的FirstName
        SELECT FirstName
        FROM PlayerTable
        GROUP BY FirstName
        HAVING COUNT(*) = 1
    ) unique_names ON p.FirstName = unique_names.FirstName
) valid_players ON s.Players = valid_players.FirstName
SET s.Players = valid_players.Email;

SQL Server

UPDATE s
SET s.Players = valid_players.Email
FROM SportsTable s
JOIN (
    SELECT p.Email, p.FirstName
    FROM PlayerTable p
    JOIN (
        SELECT FirstName
        FROM PlayerTable
        GROUP BY FirstName
        HAVING COUNT(*) = 1
    ) unique_names ON p.FirstName = unique_names.FirstName
) valid_players ON s.Players = valid_players.FirstName;

PostgreSQL

UPDATE SportsTable s
SET Players = valid_players.Email
FROM (
    SELECT p.Email, p.FirstName
    FROM PlayerTable p
    JOIN (
        SELECT FirstName
        FROM PlayerTable
        GROUP BY FirstName
        HAVING COUNT(*) = 1
    ) unique_names ON p.FirstName = unique_names.FirstName
) valid_players
WHERE s.Players = valid_players.FirstName;

效果验证 #

执行后SportsTable会更新为：

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