CODEWITHSUNDEEP
mysqlsql
POV
POV

Reputation: 12005

Table specified twice in Procedure, how to fix?

I got this message when attempted to run procedure:

enter image description here

Procedure is:

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

How to fix it?

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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:

  • 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 inner WHERE.
  • Setting variables in an EXISTS subquery simply does not make sense. EXISTS tests whether rows exist. Logically, it could execute without ever evaluating the SELECT.

Upvotes: 1

Related Questions