SET two variables in SELECT statement - MySQL

Question

I am using below code which is executing in mysql but giving error while hitting through java program as java program cannot read semicolons ... for java these are 3 statements . I need to execute this query ( setting both variable and then selecting in one query):

set @row_number:=0;set @PROMOTION_ID_NO:='';
SELECT 
     @row_number:=CASE 
        WHEN @PROMOTION_ID_NO=PD.PROMOTION_ID THEN @row_number + 1
        ELSE 1
    END AS SEQ,
     @PROMOTION_ID_NO:=PD.PROMOTION_ID AS PROMOTION_ID,
    PD.CONDITION_CODE,
    PM.PROMOTION_code,
    PD.CONDITION_TYPE
FROM
    POS_PROMOTION_DISCOUNT PD , POS_PROMOTION_MASTER PM WHERE 
    PD.PROMOTION_ID = PM.PROMOTION_ID
AND PD.STORE_NO = 'G121';

Does the posted answer work ? Please give feedback. Thanks. – Madhur Bhaiya Nov 15 '18 at 15:35 — Madhur Bhaiya, Nov 15 '18 at 15:35

score 1 · Answer 1 · answered Nov 14 '18 at 09:35

You can move the SET statement(s) to a separate Derived Table, and do a CROSS JOIN of that table with the other table(s).
Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax. I have changed to use JOIN .. ON instead.

Try the following:

SELECT 
     @row_number:=CASE 
                    WHEN @PROMOTION_ID_NO=PD.PROMOTION_ID THEN @row_number + 1
                    ELSE 1
                  END AS SEQ,
     @PROMOTION_ID_NO:=PD.PROMOTION_ID AS PROMOTION_ID,
    PD.CONDITION_CODE,
    PM.PROMOTION_code,
    PD.CONDITION_TYPE
FROM
    POS_PROMOTION_DISCOUNT PD 
JOIN POS_PROMOTION_MASTER PM ON PD.PROMOTION_ID = PM.PROMOTION_ID
CROSS JOIN (SELECT row_number:=0, @PROMOTION_ID_NO:='') AS user_init 
WHERE 
  PD.STORE_NO = 'G121';

SET two variables in SELECT statement - MySQL

1 Answers1