MySQL IF deciding multiple fields?
Depending on a complicated condition, I need to insert one or the other set of fields.
What I would like to do is (pseudo code)
IF (condition) DO
INSERT INTO table set columnA= "foo", columnB = "bar"
ELSE
INSERT INTO table set columnA= "no foo", columnB = "no bar"
ENDIF
Unfortunately I can't use stored procedures and I have to fit this into a regular statement.
I could repeat the condition for every column
INSERT INTO table (columnA, columnB) SELECT IF(condition, "foo", "no foo"), IF(condition, "bar", "no bar")
But the conditiion itself is complex and nested, so I'm trying not to repeat it.
Is there a syntax that lets a single IF or CASE decide multiple columns ?
mysql sql if-statement conditional case
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
add a comment |
Depending on a complicated condition, I need to insert one or the other set of fields.
What I would like to do is (pseudo code)
IF (condition) DO
INSERT INTO table set columnA= "foo", columnB = "bar"
ELSE
INSERT INTO table set columnA= "no foo", columnB = "no bar"
ENDIF
Unfortunately I can't use stored procedures and I have to fit this into a regular statement.
I could repeat the condition for every column
INSERT INTO table (columnA, columnB) SELECT IF(condition, "foo", "no foo"), IF(condition, "bar", "no bar")
But the conditiion itself is complex and nested, so I'm trying not to repeat it.
Is there a syntax that lets a single IF or CASE decide multiple columns ?
mysql sql if-statement conditional case
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
can't you move the condition to where clause?
– Fabricator
Feb 19 '15 at 21:49
How does this branching end up in a SQL query instead of an application? If this were only a query, without an application, you would already know the value of condition. If you're building this query and inserting parameters, you can do it in the application.
– Marcus Adams
Feb 19 '15 at 21:55
@Fabricator: Unfortunately there won't be a where claus where clause to handle the condition.
– Gene Vincent
Feb 19 '15 at 21:55
@Marcus: The SQL stateent is the output of a legacy application that I can't change. I can only specify a template for an SQL statement where the app inserts values into placeholders.
– Gene Vincent
Feb 19 '15 at 21:58
add a comment |
Depending on a complicated condition, I need to insert one or the other set of fields.
What I would like to do is (pseudo code)
IF (condition) DO
INSERT INTO table set columnA= "foo", columnB = "bar"
ELSE
INSERT INTO table set columnA= "no foo", columnB = "no bar"
ENDIF
Unfortunately I can't use stored procedures and I have to fit this into a regular statement.
I could repeat the condition for every column
INSERT INTO table (columnA, columnB) SELECT IF(condition, "foo", "no foo"), IF(condition, "bar", "no bar")
But the conditiion itself is complex and nested, so I'm trying not to repeat it.
Is there a syntax that lets a single IF or CASE decide multiple columns ?
mysql sql if-statement conditional case
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
Depending on a complicated condition, I need to insert one or the other set of fields.
What I would like to do is (pseudo code)
IF (condition) DO
INSERT INTO table set columnA= "foo", columnB = "bar"
ELSE
INSERT INTO table set columnA= "no foo", columnB = "no bar"
ENDIF
Unfortunately I can't use stored procedures and I have to fit this into a regular statement.
I could repeat the condition for every column
INSERT INTO table (columnA, columnB) SELECT IF(condition, "foo", "no foo"), IF(condition, "bar", "no bar")
But the conditiion itself is complex and nested, so I'm trying not to repeat it.
Is there a syntax that lets a single IF or CASE decide multiple columns ?
mysql sql if-statement conditional case
mysql sql if-statement conditional case
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
edited Nov 12 at 20:12
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
asked Feb 19 '15 at 21:42
Gene Vincent
2,66563375
2,66563375
can't you move the condition to where clause?
– Fabricator
Feb 19 '15 at 21:49
How does this branching end up in a SQL query instead of an application? If this were only a query, without an application, you would already know the value of condition. If you're building this query and inserting parameters, you can do it in the application.
– Marcus Adams
Feb 19 '15 at 21:55
@Fabricator: Unfortunately there won't be a where claus where clause to handle the condition.
– Gene Vincent
Feb 19 '15 at 21:55
@Marcus: The SQL stateent is the output of a legacy application that I can't change. I can only specify a template for an SQL statement where the app inserts values into placeholders.
– Gene Vincent
Feb 19 '15 at 21:58
add a comment |
can't you move the condition to where clause?
– Fabricator
Feb 19 '15 at 21:49
How does this branching end up in a SQL query instead of an application? If this were only a query, without an application, you would already know the value of condition. If you're building this query and inserting parameters, you can do it in the application.
– Marcus Adams
Feb 19 '15 at 21:55
@Fabricator: Unfortunately there won't be a where claus where clause to handle the condition.
– Gene Vincent
Feb 19 '15 at 21:55
@Marcus: The SQL stateent is the output of a legacy application that I can't change. I can only specify a template for an SQL statement where the app inserts values into placeholders.
– Gene Vincent
Feb 19 '15 at 21:58
can't you move the condition to where clause?
– Fabricator
Feb 19 '15 at 21:49
can't you move the condition to where clause?
– Fabricator
Feb 19 '15 at 21:49
How does this branching end up in a SQL query instead of an application? If this were only a query, without an application, you would already know the value of condition. If you're building this query and inserting parameters, you can do it in the application.
– Marcus Adams
Feb 19 '15 at 21:55
How does this branching end up in a SQL query instead of an application? If this were only a query, without an application, you would already know the value of condition. If you're building this query and inserting parameters, you can do it in the application.
– Marcus Adams
Feb 19 '15 at 21:55
@Fabricator: Unfortunately there won't be a where claus where clause to handle the condition.
– Gene Vincent
Feb 19 '15 at 21:55
@Fabricator: Unfortunately there won't be a where claus where clause to handle the condition.
– Gene Vincent
Feb 19 '15 at 21:55
@Marcus: The SQL stateent is the output of a legacy application that I can't change. I can only specify a template for an SQL statement where the app inserts values into placeholders.
– Gene Vincent
Feb 19 '15 at 21:58
@Marcus: The SQL stateent is the output of a legacy application that I can't change. I can only specify a template for an SQL statement where the app inserts values into placeholders.
– Gene Vincent
Feb 19 '15 at 21:58
add a comment |
4 Answers
4
active
oldest
votes
Not exactly. But it would appear that your condition is independent of the rows. You could do something like this:
INSERT INTO table(columnA, columnB)
select (case when flag = 1 then 'foo' else 'no foo' end),
(case when flag = 1 then 'bar' else 'no bar' end)
from (select <condition> as flag) x;
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
add a comment |
Variables are your friends ;-)
INSERT INTO table (columnA, columnB)
SELECT IF(@condition:=<your condition goes here>, "foo", "no foo"),
IF(@condition, "bar", "no bar");
Not that you can use EXISTS(SELECT something) as a valid condition
answered Feb 19 '15 at 22:15
Adam
11.8k1635
add a comment |
You do get a WHERE clause with a SELECT ... FROM:
You can break it into two separate queries, but, it's two queries:
INSERT INTO table (columnA, columnB)
SELECT 'foo', 'bar'
WHERE :foo;
INSERT INTO table (columnA, columnB)
SELECT 'no foo', 'no bar'
WHERE NOT :foo;
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
add a comment |
What about
INSERT INTO table
SELECT "foo", "bar" FROM something WHERE (condition)
UNION
SELECT "no foo", "no bar" FROM something WHERE NOT(condition)
answered Feb 19 '15 at 22:35
Turophile
2,8441619
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%2f28617495%2fmysql-if-deciding-multiple-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
Not exactly. But it would appear that your condition is independent of the rows. You could do something like this:
INSERT INTO table(columnA, columnB)
select (case when flag = 1 then 'foo' else 'no foo' end),
(case when flag = 1 then 'bar' else 'no bar' end)
from (select <condition> as flag) x;
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
add a comment |
Not exactly. But it would appear that your condition is independent of the rows. You could do something like this:
INSERT INTO table(columnA, columnB)
select (case when flag = 1 then 'foo' else 'no foo' end),
(case when flag = 1 then 'bar' else 'no bar' end)
from (select <condition> as flag) x;
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
add a comment |
Not exactly. But it would appear that your condition is independent of the rows. You could do something like this:
INSERT INTO table(columnA, columnB)
select (case when flag = 1 then 'foo' else 'no foo' end),
(case when flag = 1 then 'bar' else 'no bar' end)
from (select <condition> as flag) x;
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
Not exactly. But it would appear that your condition is independent of the rows. You could do something like this:
INSERT INTO table(columnA, columnB)
select (case when flag = 1 then 'foo' else 'no foo' end),
(case when flag = 1 then 'bar' else 'no bar' end)
from (select <condition> as flag) x;
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
answered Feb 19 '15 at 21:57
Gordon Linoff
758k35291399
758k35291399
add a comment |
add a comment |
Variables are your friends ;-)
INSERT INTO table (columnA, columnB)
SELECT IF(@condition:=<your condition goes here>, "foo", "no foo"),
IF(@condition, "bar", "no bar");
Not that you can use EXISTS(SELECT something) as a valid condition
answered Feb 19 '15 at 22:15
Adam
11.8k1635
add a comment |
Variables are your friends ;-)
INSERT INTO table (columnA, columnB)
SELECT IF(@condition:=<your condition goes here>, "foo", "no foo"),
IF(@condition, "bar", "no bar");
Not that you can use EXISTS(SELECT something) as a valid condition
answered Feb 19 '15 at 22:15
Adam
11.8k1635
add a comment |
Variables are your friends ;-)
INSERT INTO table (columnA, columnB)
SELECT IF(@condition:=<your condition goes here>, "foo", "no foo"),
IF(@condition, "bar", "no bar");
Not that you can use EXISTS(SELECT something) as a valid condition
answered Feb 19 '15 at 22:15
Adam
11.8k1635
Variables are your friends ;-)
INSERT INTO table (columnA, columnB)
SELECT IF(@condition:=<your condition goes here>, "foo", "no foo"),
IF(@condition, "bar", "no bar");
Not that you can use EXISTS(SELECT something) as a valid condition
answered Feb 19 '15 at 22:15
Adam
11.8k1635
edited Feb 19 '15 at 22:20
answered Feb 19 '15 at 22:15
Adam
11.8k1635
answered Feb 19 '15 at 22:15
Adam
11.8k1635
answered Feb 19 '15 at 22:15
Adam
11.8k1635
11.8k1635
add a comment |
add a comment |
You do get a WHERE clause with a SELECT ... FROM:
You can break it into two separate queries, but, it's two queries:
INSERT INTO table (columnA, columnB)
SELECT 'foo', 'bar'
WHERE :foo;
INSERT INTO table (columnA, columnB)
SELECT 'no foo', 'no bar'
WHERE NOT :foo;
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
add a comment |
You do get a WHERE clause with a SELECT ... FROM:
You can break it into two separate queries, but, it's two queries:
INSERT INTO table (columnA, columnB)
SELECT 'foo', 'bar'
WHERE :foo;
INSERT INTO table (columnA, columnB)
SELECT 'no foo', 'no bar'
WHERE NOT :foo;
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
add a comment |
You do get a WHERE clause with a SELECT ... FROM:
You can break it into two separate queries, but, it's two queries:
INSERT INTO table (columnA, columnB)
SELECT 'foo', 'bar'
WHERE :foo;
INSERT INTO table (columnA, columnB)
SELECT 'no foo', 'no bar'
WHERE NOT :foo;
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
You do get a WHERE clause with a SELECT ... FROM:
You can break it into two separate queries, but, it's two queries:
INSERT INTO table (columnA, columnB)
SELECT 'foo', 'bar'
WHERE :foo;
INSERT INTO table (columnA, columnB)
SELECT 'no foo', 'no bar'
WHERE NOT :foo;
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
answered Feb 19 '15 at 22:01
Marcus Adams
42.7k863120
42.7k863120
add a comment |
add a comment |
What about
INSERT INTO table
SELECT "foo", "bar" FROM something WHERE (condition)
UNION
SELECT "no foo", "no bar" FROM something WHERE NOT(condition)
answered Feb 19 '15 at 22:35
Turophile
2,8441619
add a comment |
What about
INSERT INTO table
SELECT "foo", "bar" FROM something WHERE (condition)
UNION
SELECT "no foo", "no bar" FROM something WHERE NOT(condition)
answered Feb 19 '15 at 22:35
Turophile
2,8441619
add a comment |
What about
INSERT INTO table
SELECT "foo", "bar" FROM something WHERE (condition)
UNION
SELECT "no foo", "no bar" FROM something WHERE NOT(condition)
answered Feb 19 '15 at 22:35
Turophile
2,8441619
What about
INSERT INTO table
SELECT "foo", "bar" FROM something WHERE (condition)
UNION
SELECT "no foo", "no bar" FROM something WHERE NOT(condition)
answered Feb 19 '15 at 22:35
Turophile
2,8441619
answered Feb 19 '15 at 22:35
Turophile
2,8441619
answered Feb 19 '15 at 22:35
Turophile
2,8441619
answered Feb 19 '15 at 22:35
Turophile
2,8441619
2,8441619
add a comment |
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%2f28617495%2fmysql-if-deciding-multiple-fields%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
can't you move the condition to where clause?
– Fabricator
Feb 19 '15 at 21:49
How does this branching end up in a SQL query instead of an application? If this were only a query, without an application, you would already know the value of condition. If you're building this query and inserting parameters, you can do it in the application.
– Marcus Adams
Feb 19 '15 at 21:55
@Fabricator: Unfortunately there won't be a where claus where clause to handle the condition.
– Gene Vincent
Feb 19 '15 at 21:55
@Marcus: The SQL stateent is the output of a legacy application that I can't change. I can only specify a template for an SQL statement where the app inserts values into placeholders.
– Gene Vincent
Feb 19 '15 at 21:58