MySQL:如何基于 SELECT 查询更新查询

Question

我有以下 SQL 查询，它在 MySQL 服务器上进行批量更新:

UPDATE res_booking AS rbg
SET rbg.host_id = (
    SELECT vw.entity_id FROM res_booking AS rb
    INNER JOIN pass_single as ps ON (ps.book_id = rb.booking_id)
    INNER JOIN voucher_who as vw ON (vw.pass_id = ps.pass_id)
    WHERE rb.booking_id = rbg.booking_id
)
WHERE rbg.host_id IS NULL;

执行它，出现以下错误:

Error Code: 1093. You can't specify target table 'rbg' for update in FROM clause

如何重写该查询才能使其正常工作？

Answer 1

我会将其写为更新连接:

UPDATE res_booking AS rbg
INNER JOIN res_booking AS rb
    ON rb.booking_id = rbg.booking_id
INNER JOIN pass_single AS ps
    ON ps.book_id = rb.booking_id
INNER JOIN voucher_who AS vw
    ON vw.pass_id = ps.pass_id
SET
    rbg.host_id = vw.entity_id
WHERE
    rbg.host_id IS NULL;

从技术上讲，res_booking 上的自联接应该是左联接，因为无论子查询是否返回值，当前的更新都将始终分配给每条记录。但是，我怀疑内部联接逻辑才是您真正想要的。