top of page

MySQL Updatable Views

In this blog, we will study you how to generate an updatable view and update data in the basic table using the view.

Starter to MySQL updatable views

In MySQL, views are not only query-able but then again also updatable. It means that you can use the INSERT or UPDATE declaration to insert or update rows of the original table using the updatable view. In addition, you can practice DELETE statement to eliminate rows of the original table through the view.

Though, to generate an updatable view, the SELECT declaration that defines the view must not cover any of the subsequent elements:

  • Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.


  • GROUP BY clause.

  • HAVING clause.

  • UNION or UNION ALL clause.

  • Left join or outer join.

  • Subquery in the SELECT section or in the WHERE section that refers to the table looked in the FROM section.

  • Position to non-updatable view in the FROM clause.

  • Position only to literal values.

  • Multiple positions to any column of the base table.

If you create a view with the TEMPTABLE algorithm, you cannot bring up-to-date the view.

Note that it is occasionally possible to create updatable views created on multiple tables using an inner join.

MySQL updatable view example

Let’s generate an updatable view.

Primary, we create a view named office Info built on the offices table in the example database. The view states to three columns of the offices table:officeCode phone, and city.

Following, we can query data from the officeInfo view using the subsequent statement:

Before, we can alter the phone number of the office with officeCode 4 over the officeInfo view using the subsequent UPDATE statement.

In conclusion, to confirm the change, we can query the data as of the officeInfo view by performing the subsequent query:

Checking updatable view info

You can crisscross if a view in a database in updatable by querying the is_updatable column from the views table in the information_schema database.

The subsequent query gets all views from the classicmodels database and shows which views are updatable.

Eliminating rows through the view

First, we generate a table named items, insert few rows into the items table, and create a view that covers items whose prices are more than 700.

Next, we use the DELETE declaration to remove a row with id value 3.

MySQL returns a message saying that 1 row(s) affected.

Third, let’s check the data through the view again.

Fourth part, we can also request the data from the base table items to confirm if the DELETE declaration deleted the row.

As you get, the row with id 3 was removed from the original table.

In this blog, we have made known you how to create an updatable view and update data in the original table using the view.

33 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


bottom of page