MySQL sub query

Question

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

Lets say I wanted to give each of my members 100 money for each post in my forum. This query works but if one member has posted more than once, they only get 100. Could someone correct this query please?

Answer 1

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

Answer 2

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 the member table, then this query can be used in your update similar to the following:

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

another issue here is when to do the update - this would best be done in a trigger so it would look more like a parameterized statement for only the current username in the current forum insert.

hth

Answer 3

Do the same as the subquery in the where clause. Instead of money=money+100 put update members m set money=money+100*(select count(f.username) from forum f where f.username = m.username)