0

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!

Community
  • 1
  • 1
Tonven
  • 608
  • 9
  • 27

2 Answers2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
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%')
shabeer
  • 1,064
  • 9
  • 17