mysql - 如何将此 SELECT 语句更改为 UPDATE 语句？

Question

我已经成功编写了一个查询，该查询可以撤回在单独的表中没有项目的订单。我现在想根据返回的记录集更新 xxx_orders。可以在一次查询中完成吗？

SELECT Orders.OrderNumber,
       Orders.CustomerNumber,
       AffiliateCode
FROM   xxx_orders AS Orders
       LEFT OUTER JOIN xxx_items AS Items
         ON Orders.OrderNumber = Items.OrderNumber
WHERE  DATEDIFF(NOW(), OrderDate) >= 14
       AND Orders.Product = 'HOUSEHOLD'
       AND STATUS = 'BOOKING'
       AND Completed = 1
       AND Items.Id IS NULL
ORDER  BY OrderDate DESC 

Answer 1

通过 MySQL 中的 JOIN 进行更新的基本语法如下所示:

UPDATE xxx_orders AS Orders
LEFT OUTER JOIN xxx_items AS Items
         ON Orders.OrderNumber = Items.OrderNumber
SET  Orders.CancelledReason = 'Expired'
WHERE  DATEDIFF(NOW(), OrderDate) >= 14
       AND Orders.Product = 'HOUSEHOLD'
       AND STATUS = 'BOOKING'
       AND Completed = 1
       AND Items.Id IS NULL

编辑:SET 出现在 WHERE 之前