Updating specific rows

A very useful feature of the SQL language, is the ability to work with date ranges, and entire tables at a time. The update statement makes it possible to update multiple columns with a single query—the same syntax works when using select queries.

To update a rows within a date range:

update my_table_name set column1='new value' where datetime_column between '01/01/2019' and '01/01/2020';

To update all rows in a table:

update my_table_name set column1='new value';

Incrementing a counter in SQL

A good example for beginners is probably how to create a counter that tracks the number of visitors on individual articles, for this, you need to keep a counter column in a table, and then increment the counter each time a page is requested. Often you will also want to filter invalid traffic, such as repeat views, and users hitting refresh within a short period of time—I am not going to show how to do that in this tutorial, but I might show it in one of the other tutorials.

Before we start, know that databases are useful for creating applications that handle concurrent users. This makes them ideal for creating a simple web page counter. In this tutorial I am using MySQL, but it should also work with other databases that support SQL.

You may be wondering if you can use a text file located somewhere in your file system for creating the counter. The answer is that you can, and it might even be faster than using a database, since your app does not have to spend time connecting to the database. The problem is, you will have to find a way to deal with concurrency on your own, and you therefor run the risk of corrupting the data. When you use a database, concurrency handling is build-in.

Consider the below table layout for a simple web-page counter:

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| url        | varchar(128) | NO   | PRI | NULL    |       |
| view_count | int          | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

This table also allows for adding new features related to articles. So, if you later want to add a word count feature, you, just create a column to store the word count in each article, this can then be updated whenever an article is posted or edited. Such a feature would allow you to keep track of your content better, and thereby identify content that does not meat your guidelines for minimum content-length.

Each article is stored in the article_stats table, and identified by the URL of the article. If we do a select on the table to return its contents, we would get something like this:

select * from article_stats;
+-----------------+------------+
| url             | view_count |
+-----------------+------------+
| /about          |      29311 |
| /privacy-policy |      12322 |
+-----------------+------------+
2 rows in set (0.00 sec)

To create this table, we can use this query:

create table article_stats (url varchar(128) primary key, view_count int);

And finally, to update the view_count on each page view we can use the update statement:

update article_stats SET view_count=view_count+1 where url='/about';

Of course, to get the requested URL we should use REQUEST_URI with parse_url to get the correct part of the URL that was requested by the client. But, that, I will save for another tutorial!

Tell us what you think: