In MySQL, views are not only query-able but also updatable. It means that you can use the INSERTor UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.
However, to create an updatable view, the SELECT statement that defines the view must not contain any of the following elements:
- Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
- DISTINCT
- GROUP BY clause.
- HAVING clause.
- UNION or UNION ALL clause.
- Left join or outer join.
- Subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause.
- Reference to non-updatable view in the FROM clause.
- Reference only to literal values.
- Multiple references to any column of the base table.
If you create a view with the TEMPTABLE algorithm, you cannot update the view.
Note that it is sometimes possible to create updatable views based on multiple tables using an inner join.
Updatable view example
Let’s create an updatable view.
First, we create a view named officeInfo based on the offices table below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE SCHEMA test_view; USE test_view; DROP TABLE IF EXISTS `offices`; CREATE TABLE `offices` ( `officeCode` varchar(10) NOT NULL, `city` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `state` varchar(50) DEFAULT NULL, `country` varchar(50) NOT NULL, `postalCode` varchar(15) NOT NULL, `territory` varchar(10) NOT NULL, PRIMARY KEY (`officeCode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*Data for the table `offices` */ insert into `offices`(`officeCode`,`city`,`phone`,`addressLine1`,`addressLine2`,`state`,`country`,`postalCode`,`territory`) values ('1','San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA'), ('2','Boston','+1 215 837 0825','1550 Court Place','Suite 102','MA','USA','02107','NA'), ('3','NYC','+1 212 555 3000','523 East 53rd Street','apt. 5A','NY','USA','10022','NA'), ('4','Paris','+33 14 723 4404','43 Rue Jouffroy D\'abbans',NULL,NULL,'France','75017','EMEA'), ('5','Tokyo','+81 33 224 5000','4-1 Kioicho',NULL,'Chiyoda-Ku','Japan','102-8578','Japan'), ('6','Sydney','+61 2 9264 2451','5-11 Wentworth Avenue','Floor #2',NULL,'Australia','NSW 2010','APAC'), ('7','London','+44 20 7877 2041','25 Old Broad Street','Level 7',NULL,'UK','EC2N 1HN','EMEA'); |
The view refers to three columns of the offices table:officeCode phone, and city.
|
1 |
CREATE VIEW officeInfo AS SELECT officeCode, phone, city FROM offices; |
Next, let query data from the officeInfo view
|
1 |
SELECT * FROM officeInfo; |
Then, we can change the phone number of the office with officeCode 4 through the officeInfo view using the following UPDATE statement.
|
1 |
UPDATE officeInfo SET phone = '+33 14 723 5555' WHERE officeCode = 4; |
To verify the change
|
1 |
SELECT * FROM officeInfo WHERE officeCode = 4; |
You can use the same approach to Insert and remove row
Removing rows through the view
You can run also a simpleDELETE statement to remove a row with id value 4.
|
1 |
delete from officeInfo where officeCode=4; |
Inserting rows through the view
|
1 2 |
INSERT INTO officeInfo (officeCode,phone,city) VALUES ('8','+00 000 0000','Benin'); INSERT INTO officeInfo (officeCode,phone,city) VALUES ('9','+00 000 0000','Chicago'); |
Checking updatable view information
You can check 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 test_view database .
|
1 |
SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = 'test_view'; |
