MySQL: setting values for all rows of a particular column in a table
The following article describes a specific case when values for all rows of a particular column must be updated with new values.
All examples below will work with the following table, let's call it "user_reputation":
+----+---------------------+----------+------------+--------+ | id | user | up_votes | down_votes | points | +----+---------------------+----------+------------+--------+ | 1 | Hubert Austin | 25 | 6 | 19 | | 2 | William Butterfield | 659 | 86 | 573 | | 3 | Sir Norman Foster | 5 | 12 | -7 | | 4 | Denys Lasdun | 1569 | 2569 | -1000 | | 5 | James Brindley | 2 | 45 | -43 | | 6 | Tommy Flowers | 36 | 35 | 1 | | 7 | William Mackenzie | 9864 | 23654 | -13790 | +----+---------------------+----------+------------+--------+
The table consists of columns:
- "id" - unique user id;
- "user" - user name;
- "up_votes" - amount of up votes given by other users;
- "down_votes" - amount of down votes given by other users;
- "points" - depends on the difference between values for "up_votes" and "down_votes".
The table above is good and simple example of data used by many websites where users rate other users (something like a reputation on some forums).
What if "up_votes" for all users must be set to 0? Then the following query must be used:
UPDATE user_reputation SET up_votes = 0;
After the query execution the data in the table will look like this:
+----+---------------------+----------+------------+--------+ | id | user | up_votes | down_votes | points | +----+---------------------+----------+------------+--------+ | 1 | Hubert Austin | 0 | 6 | 19 | | 2 | William Butterfield | 0 | 86 | 573 | | 3 | Sir Norman Foster | 0 | 12 | -7 | | 4 | Denys Lasdun | 0 | 2569 | -1000 | | 5 | James Brindley | 0 | 45 | -43 | | 6 | Tommy Flowers | 0 | 35 | 1 | | 7 | William Mackenzie | 0 | 23654 | -13790 | +----+---------------------+----------+------------+--------+
Now, values for all rows in the "points" column must be updated, because values for all rows of "up_votes" column were changed. Each value for "points" column is calculated using a formula: "up_votes" minus "down_votes".
A query which updates values for all rows of "points" column will look like this:
UPDATE user_reputation SET points = up_votes - down_votes;
Data in the table will look like this:
+----+---------------------+----------+------------+--------+ | id | user | up_votes | down_votes | points | +----+---------------------+----------+------------+--------+ | 1 | Hubert Austin | 0 | 6 | -6 | | 2 | William Butterfield | 0 | 86 | -86 | | 3 | Sir Norman Foster | 0 | 12 | -12 | | 4 | Denys Lasdun | 0 | 2569 | -2569 | | 5 | James Brindley | 0 | 45 | -45 | | 6 | Tommy Flowers | 0 | 35 | -35 | | 7 | William Mackenzie | 0 | 23654 | -23654 | +----+---------------------+----------+------------+--------+
For example, what if values for all rows of some of the columns must be updated? It's simple, values must be listed one by one separated by comma.
Here is a query which performs this operation for "up_votes", "down_votes", "points" columns:
UPDATE user_reputation SET up_votes = 0, down_votes = 0, points = up_votes – down_votes;
Data in the table will look like this:
+----+---------------------+----------+------------+--------+ | id | user | up_votes | down_votes | points | +----+---------------------+----------+------------+--------+ | 1 | Hubert Austin | 0 | 0 | 0 | | 2 | William Butterfield | 0 | 0 | 0 | | 3 | Sir Norman Foster | 0 | 0 | 0 | | 4 | Denys Lasdun | 0 | 0 | 0 | | 5 | James Brindley | 0 | 0 | 0 | | 6 | Tommy Flowers | 0 | 0 | 0 | | 7 | William Mackenzie | 0 | 0 | 0 | +----+---------------------+----------+------------+--------+
