Nested IF ELSE in MySQL

Question

I want to execute below stored procedure, but it gives me an error. Inside flag 1 after updating the order table I want to check another condition and if that's true then I want to run another update query. I tried this in SQL Server with temporary tables and it worked. please help me.

Error is below:

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 ;

You seem to be missing a semi-colon before the last "ELSE".

Jocelyn
– Jocelyn

2016-06-07 17:54:51 +00:00
Commented Jun 7, 2016 at 17:54 — Jocelyn
– Jocelyn, Commented Jun 7, 2016 at 17:54
i added a semi-colon. but still get the same error

user2837480
– user2837480

2016-06-07 18:14:54 +00:00
Commented Jun 7, 2016 at 18:14 — user2837480
– user2837480, Commented Jun 7, 2016 at 18:14

Arulkumar · Accepted Answer · 2016-06-07 18:14:39Z

1

Modify your UPDATE block as follows and try it.

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;

edited Jun 7, 2016 at 18:14

answered Jun 7, 2016 at 18:04

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2837480 Over a year ago

seems its working. i have written a RETURN in else part. so this time issue is that. actually i dont need else part here. can i just remove that else from the code...? or what should i write in else part here... thanks for your help :)

Arulkumar Over a year ago

Incase if you want to terminate the stored procedure execution and return any boolean or expression you can use RETURN, otherwise simply skip the ELSE block

wchiquito Over a year ago

This statement (RETURN) is not used in stored procedures, triggers, or events. The LEAVE statement can be used to exit a stored program of those types.

, see 14.6.5.7 RETURN Syntax.

wchiquito · Accepted Answer · 2016-06-07 18:14:37Z

1

Check the syntax of the UPDATE statement: 14.2.11 UPDATE Syntax.

.
.
.
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 -- ; <- Add semicolon
.
.
.
/*ELSE
    RETURN*/

Stored procedures do not need RETURN.

answered Jun 7, 2016 at 18:14

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Comments

Barmar · Accepted Answer · 2016-06-07 18:31:12Z

1

MySQL's UPDATE syntax does not use the FROM keyword. If you want to specify an alias for the table you're updating, just put it after the UPDATE tablename clause. And in your subquery you should be using the alias P, not PR.

UPDATE PRODUCT AS PR
INNER JOIN (SELECT P.ID AS ProductID, (P.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

There's actually no need to use the subquery, just join PRODUCT with orderdetail directly.

UPDATE PRODUCT AS PR
INNER JOIN orderdetail AS OD ON OD.ProductID = PR.ID
SET PR.Quantity = PR.Quantity - OD.Quantity
WHERE OD.OrderID = pintID

edited Jun 7, 2016 at 18:31

answered Jun 7, 2016 at 18:24

Barmar

789k57 gold badges555 silver badges669 bronze badges

3 Comments

user2837480 Over a year ago

im new to mySql. previously i used sqlserver. so i have less knowledge in mySQL syntax. your query is working. thanks for it :)

wchiquito Over a year ago

Change P.ID by PR.ID.

Barmar Over a year ago

Thanks, I thought I caught all of them.

Collectives™ on Stack Overflow

Nested IF ELSE in MySQL

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related