SQL: Update

The update SQL query is used to update rows in an existing database table. One use for the update statement is to create a edit feature on a website.

878 views
d

By. Jacob

Edited: 2021-02-05 23:45

Update, SQL

The SQL update table_name... statement is used to update existing database tables, which can be useful when we want to create an edit feature in an application. But, another concrete example of when this is useful is when creating a simple view counter, which I will cover later in this tutorial.

The update statement allows us to update all the rows in a database table at once, so it is extremely powerful, and you could break things if you are not careful when using it. Normally you would probably only want to update specific rows in the table.

To only update specific rows and/or columns, we should include a where clause in the query.

To update a specific row:

update my_table_name set column1='new value' where name_or_id="something unique :-)";

Note. Using a unique index will improve performance when working with large tables.

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:

  1. An in-dept look at the use of headings (h1-h6) and sections in HTML pages.
  2. Pagination can be a confusing thing to get right both practically and programmatically. I have put a lot of thought into this subject, and here I am giving you a few of the ideas I have been working with.
  3. The best way to deal with a trailing question mark is probably just to make it a bad request, because it is a very odd thing to find in a request URL.
  4. How to optimize image-loading and automatically include width and height attributes on img elements with PHP.
  5. HTTP headers are not case-sensitive, so we are free to convert them to all-lowercase in our applications.

More in: Web development