Home / Articles / Database management systems / MySQL / MySQL: setting values for all rows of a particular column in a table

MySQL: setting values for all rows of a particular column in a table

MySQL: setting values for all rows of a particular column in a table
 
MySQL: setting values for all rows of a particular column in a table
  • Currently 0 out of 5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Rating: 0/5 (0 votes cast)

Thank you for rating!

You have already rated this page, you can only rate it once!

Your rating has been changed, thanks for rating!

Log in or create a user account to rate this page.


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      |
+----+---------------------+----------+------------+--------+

 

Post comment

basicuse.net
html5_css
106160485398655174790

Quick navigation

General navigation