Enabling Duplicates in WHERE … IN?

user_2187548 0
Viewed 85

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
3 Answers

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.

user_41042240

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
user_17613070

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;
user_11440350
Related