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.
mysql
add a comment |
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
add a comment |
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
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 '18 at 5:41
PCGPCG
114211
114211
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 '18 at 6:01
add a comment |
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
That's cute, I will try that too.
– PCG
Nov 21 '18 at 15:52
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 '18 at 6:01
add a comment |
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.
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 '18 at 6:01
add a comment |
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.
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 '18 at 5:44
NickNick
31.5k121942
31.5k121942
Thank you. Yes, it always returns one record.listing I'd is primary key. I will check your code tommorow.
– PCG
Nov 20 '18 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 '18 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 '18 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 '18 at 6:01
add a comment |
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
That's cute, I will try that too.
– PCG
Nov 21 '18 at 15:52
add a comment |
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
That's cute, I will try that too.
– PCG
Nov 21 '18 at 15:52
add a comment |
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
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 '18 at 22:15
Nick
31.5k121942
31.5k121942
answered Nov 20 '18 at 8:54
p.ganeshp.ganesh
1124
1124
That's cute, I will try that too.
– PCG
Nov 21 '18 at 15:52
add a comment |
That's cute, I will try that too.
– PCG
Nov 21 '18 at 15:52
That's cute, I will try that too.
– PCG
Nov 21 '18 at 15:52
That's cute, I will try that too.
– PCG
Nov 21 '18 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.
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