MySQL subprocess

Question

UPDATE members 
    SET money=money+100 
WHERE username IN (    
                   SELECT username 
                     FROM forum
                  );

Suppose I wanted to give each of my members 100 money for every post on my forum. This request works, but if one participant has published several times, it receives only 100. Can someone fix this request?

Answer 1

You can use the same subquery that you used in the sentence wherein the expression itself set.

Like this

update members m 
    set money=money+100*(
                     select count(*) from forum f 
                        where f.username = m.username);

Answer 2

I think you misunderstood - your query will update all rows in memberswhere the username exists in the table forum, and not jsut the first match for each member.

Answer 3

UPDATE   SET = + 100 WHERE IN (
                  SELECT                                     );

update m set money = money + (count (f.username) * 100) m f f.username = m.username

Answer 4

I think you want this to be a dynamic query instead of updating the member table:

select username, count(username)*100 as money
from forum
group by username
order by username

if you must have it in a member table, then this query can be used in your update, as shown below:

update members m 
set money = (select count(username)*100 from forum f where f,username = m.username )

Another problem here - when to do the update - this is best done in a trigger, so that it looks more like a parameterized statement only for the current username in the current forum insert.

Hth