CODEWITHSUNDEEP
mysql
PCG
PCG

Reputation: 2299

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

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.

Upvotes: 0

Views: 42

Answers (2)

p.ganesh
p.ganesh

Reputation: 120

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

Upvotes: 0

Nick
Nick

Reputation: 147166

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.

Upvotes: 1

Related Questions