使用连接和分组进行复杂更新

Question

此查询从两个表中选择 ID，其中存在完全一对一的名称匹配

SELECT d.guest_id, h.host_id
FROM `guest` AS g 
JOIN `guest_data` AS d 
    ON d.guest_id = g.guest_id 
JOIN host_data AS h 
    ON d.guest_nm = h.host_nm 
GROUP BY
    h.venue_nm 
HAVING COUNT(*) = 1

现在我在更新 guest 表 (g) 来设置 g.link_id = h.host_id 时遇到问题

基本上我想创建像这样的声明

UPDATE `guest` AS g , `host` h
SET g.link_id = h.host_id
WHERE g.guest_id = ... AND h.host_id = ... the pairs in the previous statement

Answer 1

您可能遇到的一个问题是，每个来宾可能有许多 host_data 行，因此您必须使用聚合函数（我在下面使用了 max）来获取要从中提取 host_id 的单行。我更喜欢 SQL Server，但我认为这种语法与您将在 MySQL 上使用的语法非常接近。这是我的选择：

SELECT g.link_id, 
    (
        SELECT MAX(h.host_id)
        FROM guest_data d
        INNER JOIN host_data h ON d.guest_nm=hhost_nm
        GROUP BY h.venue_nm
        HAVING COUNT(*) = 1
    ) AS x
FROM guest g
WHERE g.guest_id IN 
(
    SELECT d.guest_id
    FROM guest_data d
    INNER JOIN host_data h ON d.guest_nm=hhost_nm
    GROUP BY h.venue_nm
    HAVING COUNT(*) = 1
)

在检查选择返回正确的结果集后，您可以轻松地将其转换为 UPDATE 语句：

UPDATE guest g
SET link_id=
    (
        SELECT MAX(h.host_id)
        FROM guest_data d
        INNER JOIN host_data h ON d.guest_nm=hhost_nm
        WHERE d.guest_id=g.guest_id
        GROUP BY h.venue_nm
        HAVING COUNT(*) = 1
    )
WHERE g.guest_id IN 
(
    SELECT d.guest_id
    FROM guest_data d
    INNER JOIN host_data h ON d.guest_nm=hhost_nm
    GROUP BY h.venue_nm
    HAVING COUNT(*) = 1
)

我希望这足够接近以提供帮助......

Answer 2

在 Sql Server 中，您可以执行类似

UPDATE guest 的 操作
设置链接 ID = 主机 ID
来自客人 g
...

我从来没有使用过mysql，所以祈祷吧。

Answer 3

稍微查看了一下语法后，我决定分解操作并使用相同的 SELECT 查询来 INSERT INTO 临时表。然后我使用典型的 JOIN 语法执行 UPDATE。

这样做的另一个好处是能够在推送到表之前验证查询结果。