MySQL IN() with duplicate rows

Question

I have the following query:

update scores as c
set c.score = c.score + 0.5
where c.user_id in (select u.acc_id from users as u, tweets as t
where u.acc_id=t.author_original_id and t.tweet_content LIKE '%music%')

As you can see I want to increase score by 0.5 for each time acc_id appears in Select query.

How to force WHERE ... IN clause to return duplicates? I can't use UNION here (as far as a can see). The similar questions have been asked before (here and there). Still, proposed solutions which require using Union, will not work here. Thanks!

Gordon Linoff · Accepted Answer · 2014-06-03 11:09:27Z

3

in cannot return duplicate rows. For that, you need to use a join. But, in an update, you don't get to update the same record multiple times -- only one of the updates has effect. Instead, count the records you want and adjust the score accordingly:

update scores s join
       (select u.acc_id, count(*) as cnt
        from users u join
             tweets t
             on u.acc_id = t.author_original_id and
                t.tweet_content LIKE '%music%'
       ) ut
       on ut.acc_id = s.user_id
    set s.score = s.score + 0.5 * cnt;

answered Jun 3, 2014 at 11:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wvdz Over a year ago

I had a similar answer in mind... but you beat me fair and square.

shabeer · Accepted Answer · 2014-06-03 11:08:58Z

0

You can use "distinct" so it will avoid duplicate.

update scores as c
set c.score = c.score + 0.5
where c.user_id in (select distinct u.acc_id from users as u, tweets as t
where u.acc_id=t.author_original_id and t.tweet_content LIKE '%music%')

answered Jun 3, 2014 at 11:08

shabeer

1,0649 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL IN() with duplicate rows

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related