在程序中指定了两次表,如何修复? [英] Table specified twice in Procedure, how to fix?
本文介绍了在程序中指定了两次表,如何修复?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在尝试运行过程时收到此消息:
I got this message when attempted to run procedure:
程序是:
BEGIN
DECLARE v_user_id INT DEFAULT 0;
DECLARE v_order_id INT DEFAULT 0;
DECLARE v_min_price INT DEFAULT 0;
UPDATE `ordersperformers` SET `ordersperformers`.`Status` = 1
WHERE EXISTS (
SELECT
@v_min_price = MIN(`ordersperformers`.`DeliveryPrice` + `ordersperformers`.`Price`), @v_user_id = `ordersperformers`.`Users_Id` = @v_user_id,
@v_order_id =`ordersperformers`.`Orders_Id`
FROM `ordersperformers`
INNER JOIN
`orders` ON `orders`.`Id` = `ordersperformers`.`Orders_Id` WHERE
NOW() <= DATE_SUB(`orders`.`DeliveryDate`, INTERVAL 2 HOUR) AND `orders`.`Status` = 0 AND `ordersperformers`.`Status` = 0
) AND `ordersperformers`.`Orders_Id` = @v_order_id AND `ordersperformers`.`Users_Id` = @v_user_id;
END
如何解决?
推荐答案
我想你想要一个相关的子查询:
I think you want a correlated subquery:
UPDATE ordersperformers op
SET op.Status = 1
WHERE op.Status = 0 AND
op.Orders_Id = @v_order_id AND
op.Users_Id = @v_user_id AND
EXISTS (SELECT 1
FROM orders o
WHERE o.id = op.Orders_Id AND
NOW() <= DATE_SUB(o.DeliveryDate, INTERVAL 2 HOUR) AND
o.Status = 0
);
我更改/修复了其他一些东西:
I changed/fixed a bunch of other things:
- 表别名使查询更易于编写和阅读.
- 反引号使查询更难编写和阅读.
- 仅在被更新的表上的条件应该在外部
WHERE
中,而不是在内部WHERE
中. - 在
EXISTS
子查询中设置变量根本没有意义.EXISTS
测试 行 是否存在.从逻辑上讲,它可以在不评估SELECT
的情况下执行.
- Table aliases make the query easier to write and to read.
- Backticks make the query harder to write and read.
- Conditions only on the table being updated should be in the outer
WHERE
, not the innerWHERE
. - Setting variables in an
EXISTS
subquery simply does not make sense.EXISTS
tests whether rows exist. Logically, it could execute without ever evaluating theSELECT
.
这篇关于在程序中指定了两次表,如何修复?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文