Enabling Duplicates in WHERE … IN?
I want to use a simple query to decrement a value in a table like so:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 2, 3, 4, 5)
This works great in examples such as the above, where the IN list contains only unique values, so each matching row has its foo column decremented by 1.
The problem is when the list contains duplicates, for example:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 3, 3, 3, 5)
In this case I would like the row where bar is 3 to be decremented three times (or by three), and 1 and 5 to be decremented by 1.
Is there a way to change the behaviour, or an alternative query that I can use where I can get the desired behaviour?
I'm specifically using MySQL 5.7, in case there are any MySQL specific workarounds that are helpful.
Update: I'm building the query in a scripting language, so feel free to provide solutions that perform any additional processing prior to running the query (perhaps as pseudo code, to be as useful to as many as possible?). I don't mind doing it this way, I just want to keep the query as simple as possible while giving the expected result.
mysql sql
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
add a comment |
I want to use a simple query to decrement a value in a table like so:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 2, 3, 4, 5)
This works great in examples such as the above, where the IN list contains only unique values, so each matching row has its foo column decremented by 1.
The problem is when the list contains duplicates, for example:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 3, 3, 3, 5)
In this case I would like the row where bar is 3 to be decremented three times (or by three), and 1 and 5 to be decremented by 1.
Is there a way to change the behaviour, or an alternative query that I can use where I can get the desired behaviour?
I'm specifically using MySQL 5.7, in case there are any MySQL specific workarounds that are helpful.
Update: I'm building the query in a scripting language, so feel free to provide solutions that perform any additional processing prior to running the query (perhaps as pseudo code, to be as useful to as many as possible?). I don't mind doing it this way, I just want to keep the query as simple as possible while giving the expected result.
mysql sql
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
1
Where does your list come from? Is it an embedded query, do you have three rows wherebar= 3, or one?
– Derrick Moeller
Nov 14 '18 at 18:12
2
Also, is it notset foo = foo - 1instead ofset foo - 1? Or is it the alternate way of writing I am unaware about?
– vivek_23
Nov 14 '18 at 18:15
What doesSET foo - 1do?
– Eric
Nov 14 '18 at 18:25
1
@vivek_23 you're absolutely right, that's just a typo on my part, I've corrected my original post!
– Haravikk
Nov 14 '18 at 18:51
@DerrickMoeller I'm building the query in PHP so I have full access to the list if a solution requires that, though it may be best to keep it as some kind of pseudo code.
– Haravikk
Nov 14 '18 at 18:51
add a comment |
I want to use a simple query to decrement a value in a table like so:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 2, 3, 4, 5)
This works great in examples such as the above, where the IN list contains only unique values, so each matching row has its foo column decremented by 1.
The problem is when the list contains duplicates, for example:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 3, 3, 3, 5)
In this case I would like the row where bar is 3 to be decremented three times (or by three), and 1 and 5 to be decremented by 1.
Is there a way to change the behaviour, or an alternative query that I can use where I can get the desired behaviour?
I'm specifically using MySQL 5.7, in case there are any MySQL specific workarounds that are helpful.
Update: I'm building the query in a scripting language, so feel free to provide solutions that perform any additional processing prior to running the query (perhaps as pseudo code, to be as useful to as many as possible?). I don't mind doing it this way, I just want to keep the query as simple as possible while giving the expected result.
mysql sql
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
I want to use a simple query to decrement a value in a table like so:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 2, 3, 4, 5)
This works great in examples such as the above, where the IN list contains only unique values, so each matching row has its foo column decremented by 1.
The problem is when the list contains duplicates, for example:
UPDATE `Table`
SET `foo` = `foo` - 1
WHERE `bar` IN (1, 3, 3, 3, 5)
In this case I would like the row where bar is 3 to be decremented three times (or by three), and 1 and 5 to be decremented by 1.
Is there a way to change the behaviour, or an alternative query that I can use where I can get the desired behaviour?
I'm specifically using MySQL 5.7, in case there are any MySQL specific workarounds that are helpful.
Update: I'm building the query in a scripting language, so feel free to provide solutions that perform any additional processing prior to running the query (perhaps as pseudo code, to be as useful to as many as possible?). I don't mind doing it this way, I just want to keep the query as simple as possible while giving the expected result.
mysql sql
mysql sql
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
edited Nov 14 '18 at 18:50
Haravikk
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
asked Nov 14 '18 at 18:07
HaravikkHaravikk
1,35711432
1,35711432
1
Where does your list come from? Is it an embedded query, do you have three rows wherebar= 3, or one?
– Derrick Moeller
Nov 14 '18 at 18:12
2
Also, is it notset foo = foo - 1instead ofset foo - 1? Or is it the alternate way of writing I am unaware about?
– vivek_23
Nov 14 '18 at 18:15
What doesSET foo - 1do?
– Eric
Nov 14 '18 at 18:25
1
@vivek_23 you're absolutely right, that's just a typo on my part, I've corrected my original post!
– Haravikk
Nov 14 '18 at 18:51
@DerrickMoeller I'm building the query in PHP so I have full access to the list if a solution requires that, though it may be best to keep it as some kind of pseudo code.
– Haravikk
Nov 14 '18 at 18:51
add a comment |
1
Where does your list come from? Is it an embedded query, do you have three rows wherebar= 3, or one?
– Derrick Moeller
Nov 14 '18 at 18:12
2
Also, is it notset foo = foo - 1instead ofset foo - 1? Or is it the alternate way of writing I am unaware about?
– vivek_23
Nov 14 '18 at 18:15
What doesSET foo - 1do?
– Eric
Nov 14 '18 at 18:25
1
@vivek_23 you're absolutely right, that's just a typo on my part, I've corrected my original post!
– Haravikk
Nov 14 '18 at 18:51
@DerrickMoeller I'm building the query in PHP so I have full access to the list if a solution requires that, though it may be best to keep it as some kind of pseudo code.
– Haravikk
Nov 14 '18 at 18:51
1
1
Where does your list come from? Is it an embedded query, do you have three rows where
bar = 3, or one?– Derrick Moeller
Nov 14 '18 at 18:12
Where does your list come from? Is it an embedded query, do you have three rows where
bar = 3, or one?– Derrick Moeller
Nov 14 '18 at 18:12
2
2
Also, is it not
set foo = foo - 1 instead of set foo - 1? Or is it the alternate way of writing I am unaware about?– vivek_23
Nov 14 '18 at 18:15
Also, is it not
set foo = foo - 1 instead of set foo - 1? Or is it the alternate way of writing I am unaware about?– vivek_23
Nov 14 '18 at 18:15
What does
SET foo - 1 do?– Eric
Nov 14 '18 at 18:25
What does
SET foo - 1 do?– Eric
Nov 14 '18 at 18:25
1
1
@vivek_23 you're absolutely right, that's just a typo on my part, I've corrected my original post!
– Haravikk
Nov 14 '18 at 18:51
@vivek_23 you're absolutely right, that's just a typo on my part, I've corrected my original post!
– Haravikk
Nov 14 '18 at 18:51
@DerrickMoeller I'm building the query in PHP so I have full access to the list if a solution requires that, though it may be best to keep it as some kind of pseudo code.
– Haravikk
Nov 14 '18 at 18:51
@DerrickMoeller I'm building the query in PHP so I have full access to the list if a solution requires that, though it may be best to keep it as some kind of pseudo code.
– Haravikk
Nov 14 '18 at 18:51
add a comment |
3 Answers
3
active
oldest
votes
If you can process your original list first to get the counts, you could dynamically construct this kind of query:
UPDATE `Table`
SET `foo` = `foo` - CASE `bar` WHEN 1 THEN 1 WHEN 3 THEN 3 WHEN 5 THEN 1 ELSE 0 END
WHERE `bar` IN (1, 3, 5)
;
Note: the ELSE is just being thorough/paranoid; the WHERE should prevent it from ever getting that far.
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
add a comment |
There is an example might be beneficial for your purpose:
create table #temp (value int)
create table #mainTable (id int, mainValue int)
insert into #temp (value) values (1),(3),(3),(3),(4)
insert into #mainTable values (1,5),(2,5),(3,5),(4,5)
select value,count(*) as AddValue
into #otherTemp
from #temp t
group by value
update m
set mainValue = m.mainValue+ ot.AddValue
from #otherTemp ot
inner join #mainTable m on m.id=ot.value
select * from #mainTable
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
add a comment |
This is a little tricky, but you can do it by aggregating first:
update table t join
(select bar, count(*) as factor
from (select 1 as bar union all select 3 as bar union all select 3 as bar union all select 3 as bar union all select 5
) b
) b
on t.bar = b.bar
t.foo = t.foo - bar.factor;
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
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%2f53306361%2fenabling-duplicates-in-where-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you can process your original list first to get the counts, you could dynamically construct this kind of query:
UPDATE `Table`
SET `foo` = `foo` - CASE `bar` WHEN 1 THEN 1 WHEN 3 THEN 3 WHEN 5 THEN 1 ELSE 0 END
WHERE `bar` IN (1, 3, 5)
;
Note: the ELSE is just being thorough/paranoid; the WHERE should prevent it from ever getting that far.
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
add a comment |
If you can process your original list first to get the counts, you could dynamically construct this kind of query:
UPDATE `Table`
SET `foo` = `foo` - CASE `bar` WHEN 1 THEN 1 WHEN 3 THEN 3 WHEN 5 THEN 1 ELSE 0 END
WHERE `bar` IN (1, 3, 5)
;
Note: the ELSE is just being thorough/paranoid; the WHERE should prevent it from ever getting that far.
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
add a comment |
If you can process your original list first to get the counts, you could dynamically construct this kind of query:
UPDATE `Table`
SET `foo` = `foo` - CASE `bar` WHEN 1 THEN 1 WHEN 3 THEN 3 WHEN 5 THEN 1 ELSE 0 END
WHERE `bar` IN (1, 3, 5)
;
Note: the ELSE is just being thorough/paranoid; the WHERE should prevent it from ever getting that far.
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
If you can process your original list first to get the counts, you could dynamically construct this kind of query:
UPDATE `Table`
SET `foo` = `foo` - CASE `bar` WHEN 1 THEN 1 WHEN 3 THEN 3 WHEN 5 THEN 1 ELSE 0 END
WHERE `bar` IN (1, 3, 5)
;
Note: the ELSE is just being thorough/paranoid; the WHERE should prevent it from ever getting that far.
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
answered Nov 14 '18 at 19:45
UueerdoUueerdo
11.9k1816
11.9k1816
add a comment |
add a comment |
There is an example might be beneficial for your purpose:
create table #temp (value int)
create table #mainTable (id int, mainValue int)
insert into #temp (value) values (1),(3),(3),(3),(4)
insert into #mainTable values (1,5),(2,5),(3,5),(4,5)
select value,count(*) as AddValue
into #otherTemp
from #temp t
group by value
update m
set mainValue = m.mainValue+ ot.AddValue
from #otherTemp ot
inner join #mainTable m on m.id=ot.value
select * from #mainTable
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
add a comment |
There is an example might be beneficial for your purpose:
create table #temp (value int)
create table #mainTable (id int, mainValue int)
insert into #temp (value) values (1),(3),(3),(3),(4)
insert into #mainTable values (1,5),(2,5),(3,5),(4,5)
select value,count(*) as AddValue
into #otherTemp
from #temp t
group by value
update m
set mainValue = m.mainValue+ ot.AddValue
from #otherTemp ot
inner join #mainTable m on m.id=ot.value
select * from #mainTable
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
add a comment |
There is an example might be beneficial for your purpose:
create table #temp (value int)
create table #mainTable (id int, mainValue int)
insert into #temp (value) values (1),(3),(3),(3),(4)
insert into #mainTable values (1,5),(2,5),(3,5),(4,5)
select value,count(*) as AddValue
into #otherTemp
from #temp t
group by value
update m
set mainValue = m.mainValue+ ot.AddValue
from #otherTemp ot
inner join #mainTable m on m.id=ot.value
select * from #mainTable
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
There is an example might be beneficial for your purpose:
create table #temp (value int)
create table #mainTable (id int, mainValue int)
insert into #temp (value) values (1),(3),(3),(3),(4)
insert into #mainTable values (1,5),(2,5),(3,5),(4,5)
select value,count(*) as AddValue
into #otherTemp
from #temp t
group by value
update m
set mainValue = m.mainValue+ ot.AddValue
from #otherTemp ot
inner join #mainTable m on m.id=ot.value
select * from #mainTable
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
answered Nov 14 '18 at 18:26
Eray BalkanliEray Balkanli
4,18942044
4,18942044
add a comment |
add a comment |
This is a little tricky, but you can do it by aggregating first:
update table t join
(select bar, count(*) as factor
from (select 1 as bar union all select 3 as bar union all select 3 as bar union all select 3 as bar union all select 5
) b
) b
on t.bar = b.bar
t.foo = t.foo - bar.factor;
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
add a comment |
This is a little tricky, but you can do it by aggregating first:
update table t join
(select bar, count(*) as factor
from (select 1 as bar union all select 3 as bar union all select 3 as bar union all select 3 as bar union all select 5
) b
) b
on t.bar = b.bar
t.foo = t.foo - bar.factor;
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
add a comment |
This is a little tricky, but you can do it by aggregating first:
update table t join
(select bar, count(*) as factor
from (select 1 as bar union all select 3 as bar union all select 3 as bar union all select 3 as bar union all select 5
) b
) b
on t.bar = b.bar
t.foo = t.foo - bar.factor;
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
This is a little tricky, but you can do it by aggregating first:
update table t join
(select bar, count(*) as factor
from (select 1 as bar union all select 3 as bar union all select 3 as bar union all select 3 as bar union all select 5
) b
) b
on t.bar = b.bar
t.foo = t.foo - bar.factor;
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
answered Nov 14 '18 at 19:24
Gordon LinoffGordon Linoff
765k35296400
765k35296400
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.
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%2f53306361%2fenabling-duplicates-in-where-in%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
1
Where does your list come from? Is it an embedded query, do you have three rows where
bar= 3, or one?– Derrick Moeller
Nov 14 '18 at 18:12
2
Also, is it not
set foo = foo - 1instead ofset foo - 1? Or is it the alternate way of writing I am unaware about?– vivek_23
Nov 14 '18 at 18:15
What does
SET foo - 1do?– Eric
Nov 14 '18 at 18:25
1
@vivek_23 you're absolutely right, that's just a typo on my part, I've corrected my original post!
– Haravikk
Nov 14 '18 at 18:51
@DerrickMoeller I'm building the query in PHP so I have full access to the list if a solution requires that, though it may be best to keep it as some kind of pseudo code.
– Haravikk
Nov 14 '18 at 18:51