How to assign column value to a variable within mySQL script ?
up vote
0
down vote
favorite
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.
mysql
asked Nov 20 at 5:41
PCG
85111
add a comment |
up vote
0
down vote
favorite
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.
mysql
asked Nov 20 at 5:41
PCG
85111
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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.
mysql
asked Nov 20 at 5:41
PCG
85111
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.
mysql
mysql
asked Nov 20 at 5:41
PCG
85111
asked Nov 20 at 5:41
PCG
85111
asked Nov 20 at 5:41
PCG
85111
asked Nov 20 at 5:41
PCG
85111
asked Nov 20 at 5:41
PCG
85111
85111
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
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 at 5:44
Nick
21.3k61435
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 at 6:01
add a comment |
up vote
0
down vote
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 at 22:15
Nick
21.3k61435
answered Nov 20 at 8:54
p.ganesh
1124
That's cute, I will try that too.
– PCG
Nov 21 at 15:52
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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 at 5:44
Nick
21.3k61435
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 at 6:01
add a comment |
up vote
1
down vote
accepted
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 at 5:44
Nick
21.3k61435
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 at 6:01
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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 at 5:44
Nick
21.3k61435
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 at 5:44
Nick
21.3k61435
answered Nov 20 at 5:44
Nick
21.3k61435
answered Nov 20 at 5:44
Nick
21.3k61435
answered Nov 20 at 5:44
Nick
21.3k61435
21.3k61435
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 at 6:01
add a comment |
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 at 6:01
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 at 6:01
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 at 6:01
add a comment |
up vote
0
down vote
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 at 22:15
Nick
21.3k61435
answered Nov 20 at 8:54
p.ganesh
1124
That's cute, I will try that too.
– PCG
Nov 21 at 15:52
add a comment |
up vote
0
down vote
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 at 22:15
Nick
21.3k61435
answered Nov 20 at 8:54
p.ganesh
1124
That's cute, I will try that too.
– PCG
Nov 21 at 15:52
add a comment |
up vote
0
down vote
up vote
0
down vote
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 at 22:15
Nick
21.3k61435
answered Nov 20 at 8:54
p.ganesh
1124
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 at 22:15
Nick
21.3k61435
answered Nov 20 at 8:54
p.ganesh
1124
edited Nov 21 at 22:15
Nick
21.3k61435
edited Nov 21 at 22:15
Nick
21.3k61435
edited Nov 21 at 22:15
Nick
21.3k61435
21.3k61435
answered Nov 20 at 8:54
p.ganesh
1124
answered Nov 20 at 8:54
p.ganesh
1124
answered Nov 20 at 8:54
p.ganesh
1124
1124
That's cute, I will try that too.
– PCG
Nov 21 at 15:52
add a comment |
That's cute, I will try that too.
– PCG
Nov 21 at 15:52
That's cute, I will try that too.
– PCG
Nov 21 at 15:52
That's cute, I will try that too.
– PCG
Nov 21 at 15:52
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53386887%2fhow-to-assign-column-value-to-a-variable-within-mysql-script%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
