如果我连接两个表，Mysql 更新查询限制不起作用

Question

在 MYSQL 中，如果我在连接两个表时设置更新限制，则会显示

ERROR 1221 (HY000): Incorrect usage of UPDATE and LIMIT

update 
       order_product_mapping as opm, 
       order_details as od, 
       product as p 
   set fullfillment='Y' 
 where 
       p.product_id=opm.product_id 
       AND od.order_id=opm.order_id 
       AND od.order_id=100 
       AND p.product_id=1 limit 1;

我的表架构是
order_details 表

CREATE TABLE `order_details` (
  `order_id` int(5) NOT NULL AUTO_INCREMENT,
  `amount` int(5) DEFAULT NULL,
  `order_status` char(1) DEFAULT 'N',
  `company_order_id` int(5) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)

产品表

CREATE TABLE `product` (
  `product_id` int(5) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(50) DEFAULT NULL,
  `product_amount` int(5) DEFAULT NULL,
  `product_status` char(1) DEFAULT 'N',
  `company_product_id` int(5) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
)

订单产品映射

CREATE TABLE `order_product_mapping` (
  `product_id` int(5) DEFAULT NULL,
  `order_id` int(5) DEFAULT NULL,
  `fulfillment` char(1) DEFAULT 'N'
)

我将获取company_order_id和company_product_id作为输入，以便我进行更新查询以更改order_product_mapping中的履行状态。如果在查询中添加限制，则会显示错误。

Answer 1

请参阅此帖子以获取错误说明:https://stackoverflow.com/a/4291851/1073631

我确实认为我已经找到了解决方法/黑客。连接 order_product_mapping 表本身并创建行号。使用行号 = 1 的工作方式应与 LIMIT 1 相同:

UPDATE order_product_mapping opm
  JOIN (SELECT *, @rowNum:=@rowNum+1 rn FROM order_product_mapping JOIN (SELECT @rowNum:= 0) r)
    as opm2 ON opm.product_id = opm2.product_id and opm.order_id = opm2.order_id
  JOIN product p ON p.product_id=opm.product_id AND p.product_id=1
  JOIN order_details as od ON od.order_id=opm.order_id 
SET opm.fulfillment = 'Y'
where od.order_id=100 
And rn = 1;

还有一些 fiddle 样本:http://sqlfiddle.com/#!2/03f12/1