mysql - MySQL 中的嵌套 IF ELSE

Question

我想执行下面的存储过程，但它给我一个错误。更新订单表后在标志 1 内，我想检查另一个条件，如果是，那么我想运行另一个更新查询。我在 SQL Server 中使用临时表尝试了这个并且它有效。请帮我。

错误如下:

Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM product PR INNER JOIN (SELECT PR.ID AS ProductID, (PR.Quantity - OD.Qu' at line 35

DELIMITER $$

DROP PROCEDURE IF EXISTS `onlineshop`.`USP_Public_SaveOrder` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `USP_Public_SaveOrder`(IN pint_Flag INT,INOUT pint_ID BIGINT,IN pint_Status INT,IN pint_CustomerID BIGINT,IN pint_ShippingAddressID BIGINT,IN pint_BillingAddresID BIGINT, IN pdec_ShippingCharge DECIMAL(18,2),IN pdec_Tax DECIMAL(18,2),IN pdec_Total DECIMAL(18,2),IN pdte_TransactionDate DATETIME)
BEGIN
IF(pint_Flag=0) THEN
    INSERT INTO orderheader(Status,
                                            CustomerID,
                                            ShippingAddressID,
                                            BillingAddressID,
                                            ShippingCharge,
                                            Tax,
                                            Total,
                                            TransactionDate)
                            VALUES (pint_Status,
                                            pint_CustomerID,
                                            pint_ShippingAddressID,
                                            pint_BillingAddresID,
                                            pdec_ShippingCharge,
                                            pdec_Tax,
                                            pdec_Total,
                                            pdte_TransactionDate);
        SET pint_ID=LAST_INSERT_ID();

ELSEIF(pint_Flag=1) THEN
    UPDATE orderheader
    SET     Status = pint_Status,
                CustomerID = pint_CustomerID,
                ShippingAddressID = pint_ShippingAddressID,
                BillingAddressID = pint_BillingAddresID,
                ShippingCharge = pdec_ShippingCharge,
                Tax = pdec_Tax,
                Total = pdec_Total,
                TransactionDate = pdte_TransactionDate
    WHERE   ID=pint_ID;
  IF(pint_Status=2) THEN
    UPDATE product SET Quantity = A.remain
    FROM product PR
    INNER JOIN (SELECT PR.ID AS ProductID, (PR.Quantity - OD.Quantity) AS remain
              FROM product P
              INNER JOIN orderdetail OD ON OD.ProductID = P.ID AND OD.OrderID = pint_ID) A ON PR.ID = A.ProductID
  ELSE
    RETURN
  END IF;
END IF;
END $$

DELIMITER ;

Answer 1

如下修改您的 UPDATE block 并尝试。

IF(pint_Status=2) THEN
    UPDATE product PR
    INNER JOIN (SELECT PR.ID AS ProductID, (PR.Quantity - OD.Quantity) AS remain
                FROM product P
                INNER JOIN orderdetail OD ON OD.ProductID = P.ID AND OD.OrderID = pint_ID
               ) A ON PR.ID = A.ProductID
    SET PR.Quantity = A.remain;
ELSE
    RETURN
END IF;