mysql - 如何在子查询中使用该行的同时更新行中的值？

Question

我正在尝试制作一个房间预订系统，一旦有人请求预订，他们就会收到一个密码，并且在他们发回该密码之前，预订不会得到确认。

我已经达到用户可以请求预订的地步，它会生成一个密码并将其添加到正确的表格中，但是如果密码和用户名，我在尝试更新预订状态时遇到困难他们输入的密码与预订组合相匹配。

用户将输入他们的用户名和密码，我想:

1. 将 b_status(p_bookingId 与 b_id 匹配的预订)更新为“已确认”
2. 删除密码表中包含密码的行。

在我下面的表格示例中:

1. 有人会输入“jeremy”和“546365”
2. b_id=2 行的 b_status 将更新为“已确认”
3. 引脚表中的行将被删除

我有 3 个表:'users'、'bookings' 和 'pin'。

users
------------------
u_id | u_username
------------------
1    | jane
2    | johnny
3    | jeremy

.

bookings
---------------------------------
b_id | b_userId | b_status
---------------------------------
1    | 2        | pending confirm
2    | 3        | pending confirm

.

pins
--------------------------------
p_id | p_bookingId | p_pinNumber
--------------------------------
1    | 2           | 546365

Answer 1

从有助于事务操作的表中删除行不是最佳做法。

与其删除 PIN，不如在 pins 表中添加一列来存储 PIN 使用日期，例如 p_pinUsedDate(从而标记 PIN作为使用)。

使用变量查找正确的预订 ID，然后更新 bookings 和 pin 表:

SELECT @b := p_bookingId

FROM bookings b
INNER JOIN pins p
    ON p.p_bookingId = b.b_id
INNER JOIN users u
    ON b.b_userId = u.u_id

WHERE u.u_username = 'jeremy'
AND p.p_pinNumber = '546365'
AND p.p_pinUsedDate IS NULL


IF @b IS NOT NULL THEN

    UPDATE bookings b
    SET b_status = 'confirmed'
    WHERE b_id = @b

    UPDATE pins p
    SET p_pinUsedDate = NOW()
    WHERE p_pinNumber = '546365'
    AND p_bookingId = @b
    AND p.p_pinUsedDate IS NULL

END IF