mysql query need to be optimized

Question

I have a query which give result like

id | productid | userid | coinsid
1  | 2         | 2      |  5     
3  | 2         | 2      |  6      
4  | 2         | 3      |  7
5  | 2         | 4      |  8
6  | 2         | 3      |  9

This is result for specific productid. Now i have to update the balance in user table by adding $1 to all the users in above result, but if userid is twice, i need to add $1 twice to the balance of that specific user. So in the above case $1 twice added to userid=2 balance and userid=3 balance.

The simple way is to count records for every distinct userid and run queries as many time as we have users in foreach loop. But i am looking for some optimize way. Please suggest any. Thanks

Answer 1

One approach:

UPDATE user_table u
  JOIN ( SELECT q.userid
              , SUM(1.00) AS deposit
           FROM (
                  -- original OP query goes here
                ) q
          GROUP BY q.userid
       ) r
    ON r.userid = u.userid 
   SET u.balance = u.balance + r.deposit

We use the original OP query that returns the resultset displayed, and make that an inline view (aliased in the query above as q).

From that, we query a distinct list of userid, and the number of times that userid appears in the resultset. That gives us the username and a deposit amount (1 dollar for each time the userid appears) (some databases might want us to specify the value as 1.0 rather than 1, to make sure it was decimal. I think the SUM is more representative of what we are trying to accomplish.)

We join that inline view (r) to the user table, and add the deposit amount to the current balance, for that user (assuming the balance is stored as decimal dollars (1.00 = one dollar)

---

To testing, convert the UPDATE into a SELECT statement:

• remove the "SET" clause
• add an "ORDER BY" clause (optional) to make the results determinate
• remove the "UPDATE" keyword and replace it

with:

 SELECT r.userid
      , r.deposit
      , u.balance             AS old_balance
      , u.balance + r.deposit AS new_balance
      , u.userid
   FROM

---

Full select:

 SELECT r.userid
      , r.deposit
      , u.balance             AS old_balance
      , u.balance + r.deposit AS new_balance
      , u.userid
   FROM user_table u
  JOIN ( SELECT q.userid
              , SUM(1.00) AS deposit
           FROM (
                  -- original OP query goes here
                ) q
          GROUP BY q.userid
       ) r
    ON r.userid = u.userid

NOTE There is no WHERE clause, the JOIN predicates (in the ON clause) is what determines which rows are selected/affected in the user table.