What will be the SQL query for this UPDATE operation?

Question

I am using MySql 5.1. i have to database tables Users and Users_group_mapping.

User Table :
    userid, username,   points
     1       user1       10
     2       user2       21
     3       user3       7
     4       user4       44

Users_group_mapping Table  :
    userid, usergroupid
     1       1
     2       2
     4       2
     4       1
     4       3

And in an allocation Process i have to allocate points to corresponding groups users. For that test data is like :

Data : $dtArr = array(
    [1] => 40,
    [2] => 80,
    [3] => 100
)

In array $dtArr, the key is the attribute 'usergroupdid' in Users_group_mapping table and the value is the attribute points that to be updated in Users table.

e.g. if usergroupid = 2 and points to be given = 40 then from Users_group_mapping table the users in usergroup '2' - which are userids '2' & '4' - both users will get 40 points each. userid '4' will get only 40 points allocated through usergroup '2' even though the user belongs to usergoups '1' & '3' also.

What will be the SQL Update query for this operation?

Please guide me..!! thanks in advance.!!

Answer 1

If you have everything in database and if you really want to do it in order given by table Users_group_mapping, than you should add sequence column to this table and create new table Data. So you will have something like this:

User Table :
    userid, username,   points
     1       user1       10
     2       user2       21
     3       user3       7
     4       user4       44

Users_group_mapping Table  :
    userid, usergroupid  rowseq
     1       1            1
     2       2            2
     4       2            3
     4       1            4
     4       3            5

Data Table  :
    usergroupid, points
     1             40
     2             80  
     3            100

than the SQL which you require is as follows:

UPDATE User 
  JOIN Users_group_mapping USING (userid) 
  JOIN Data on Data.usergroupid=Users_group_mapping.usergroupid
  LEFT JOIN Users_group_mapping as x ON x.userid=User.userid and x.rowseq < Users_group_mapping.rowseq 
  SET User.points = User.points + Data.points
  WHERE x.userid IS NULL

if you do not want to add points to already existing points, but set them, than of course use

UPDATE User 
  JOIN Users_group_mapping USING (userid) 
  JOIN Data on Data.usergroupid=Users_group_mapping.usergroupid
  LEFT JOIN Users_group_mapping as x ON x.userid=User.userid and x.rowseq < Users_group_mapping.rowseq 
  SET User.points = Data.points
  WHERE x.userid IS NULL

EDIT

Here is the solution if you can not change the data structure (php+mysql)

$userids[] = array();
$result = mysql_query("SELECT * FROM User");

while ($row = mysql_fetch_assoc($result)) {
  $userids[] = $user_row['userid'];
};

foreach($userids as $userid) {
  $result = mysql_query("SELECT usergroupid FROM  Users_group_mapping WHERE userid = $userid");
  $row = mysql_fetch_assoc($result));   # we only need first matching row so no cycle
  $usergroupid = intval($row['usergroupid']);
  $points = $dtArr[$usergroupid];
  mysql_query("UPDATE User SET points = points + $points WHERE User.userid = $userid");   
};

please not that although the code is correct, because it is not that hard to solve this, there will be some stupid mistakes (syntax errors etc) as I do not have time to debug it. But it should give you your answer.