How to assign column value to a variable within mySQL script ?

Question

I have the following script but it returns null all the time.

    SELECT 
    @PRICE_LARGE_PRICE = PRICE_LARGE_PRICE,
    @PRICE_SMALL_PRICE = PRICE_SMALL_PRICE
FROM
    prices
WHERE
    PRICE_LISTING_ID = 60;

SET @ITEM_PRICE = (CASE Size WHEN GivenLargeSizeName THEN @PRICE_LARGE_PRICE 
WHEN GivenSmallSizeName THEN @PRICE_SMALL_PRICE 
ELSE null 
END);

The issue here is

@PRICE_LARGE_PRICE = PRICE_LARGE_PRICE,
@PRICE_SMALL_PRICE = PRICE_SMALL_PRICE

table returns PRICE_LARGE_PRICE & PRICE_SMALL_PRICE correctly but the assignment does not work. Hence CASE fails.

Any help is appreciated.

Nick · Accepted Answer · 2018-11-20 05:44:14Z

1

You need to use SELECT ... INTO:

SELECT PRICE_LARGE_PRICE, PRICE_SMALL_PRICE
INTO @PRICE_LARGE_PRICE, @PRICE_SMALL_PRICE
FROM prices
WHERE PRICE_LISTING_ID = 60;

Note that you need to ensure that the query only returns one row of data, using LIMIT 1 if necessary.

answered Nov 20, 2018 at 5:44

Nick

147k23 gold badges67 silver badges106 bronze badges

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

1 Comment

PCG Over a year ago

Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.

Nick · Accepted Answer · 2018-11-21 22:15:46Z

0

SELECT 
    @PRICE_LARGE_PRICE:=PRICE_LARGE_PRICE,
    @PRICE_SMALL_PRICE:=PRICE_SMALL_PRICE
FROM
    prices
WHERE
    PRICE_LISTING_ID = 60;

just add colon before equal sign in mysql

edited Nov 21, 2018 at 22:15

Nick

147k23 gold badges67 silver badges106 bronze badges

answered Nov 20, 2018 at 8:54

p.ganesh

1204 bronze badges

1 Comment

PCG Over a year ago

That's cute, I will try that too.

Collectives™ on Stack Overflow

How to assign column value to a variable within mySQL script ?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related