Which of the Following Can Be Used With an Updatable View?

Summary: in this tutorial, nosotros will show you how to create an updatable view and update data in the underlying table through the view.

Introduction to MySQL updatable views

In MySQL, views are not only query-able but also updatable. Information technology means that you tin use the INSERT or UPDATE argument to insert or update rows of the base table through the updatable view. In addition, you can use DELETE argument to remove rows of the underlying tabular array through the view.

Withal, to create an updatable view, the SELECT statement that defines the view must not contain whatsoever of the post-obit elements:

• Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
• Distinct
• GROUP Past clause.
• HAVING clause.
• Matrimony or UNION ALL clause.
• Left join or outer join.
• Subquery in the SELECT clause or in the WHERE clause that refers to the tabular array appeared in the FROM clause.
• Reference to not-updatable view in the FROM clause.
• Reference only to literal values.
• Multiple references to any cavalcade of the base tabular array.

If you create a view with the TEMPTABLE algorithm, y'all cannot update the view.

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

MySQL updatable view case

Let's create an updatable view.

Get-go, we create a view named officeInfo based on the offices table in the sample database. The view refers to three columns of the offices table:officeCode phone, and city.

            
                              CREATE                VIEW                officeInfo                Equally                SELECT                officeCode, telephone, city                FROM                offices;            
                          Lawmaking language:              SQL (Structured Query Language)              (              sql              )                      

Adjacent, we can query information from the officeInfo view using the following statement:

            
                              SELECT                *                FROM                officeInfo;            
                          Lawmaking language:              SQL (Structured Query Language)              (              sql              )                      

Then, we can change the telephone number of the office with officeCode 4 through the officeInfo view using the following UPDATE statement.

            
                              UPDATE                officeInfo                Set                telephone =                '+33 xiv 723 5555'                WHERE                officeCode =                4;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Finally, to verify the change, we can query the data from the officeInfo view by executing the following query:

            
                              SELECT                *                FROM                officeInfo                WHERE                officeCode =                iv;            
                          Code linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )                      

Checking updatable view information

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

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

            
                              SELECT                table_name,      is_updatable                FROM                information_schema.views                WHERE                table_schema =                'classicmodels';            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )                      

Removing rows through the view

First, nosotros create a table named items, insert some rows into the items table, and create a view that contains items whose prices are greater than 700.

            
                              -- create a new table named items                CREATE                TABLE                items (                id                INT                AUTO_INCREMENT Primary                KEY,                name                VARCHAR(100)                Not                NULL,     price                DECIMAL(11                ,                2                )                NOT                Nothing                );                -- insert information into the items table                INSERT                INTO                items(name,price)                VALUES('Laptop',700.56),('Desktop',699.99),('iPad',700.l) ;                -- create a view based on items tabular array                CREATE                VIEW                LuxuryItems                AS                SELECT                *                FROM                items                WHERE                price >                700;                -- query data from the LuxuryItems view                SELECT                *                FROM                LuxuryItems;            
                          Lawmaking language:              SQL (Structured Query Language)              (              sql              )                      

2nd, nosotros use the DELETE statement to remove a row with id value 3.

            
                              DELETE                FROM                LuxuryItems                WHERE                id                =                3;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

MySQL returns a bulletin proverb that i row(s) affected.

Third, let's cheque the data through the view again.

            
                              SELECT                *                FROM                LuxuryItems;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Quaternary, we can also query the data from the base of operations tabular array items to verify if the DELETE statement actually deleted the row.

            
                              SELECT                *                FROM                items;            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )                      

As you encounter, the row with id iii was removed from the base of operations table.

In this tutorial, nosotros have shown you how to create an updatable view and update information in the underlying table through the view.

Was this tutorial helpful?

youngbloodwitally.blogspot.com

Source: https://www.mysqltutorial.org/create-sql-updatable-views.aspx

Share this post