After updating any value in a particular view, will MySQL updates the same in the base table and its associated views (if any)?

MySQLMySQLi Database

Yes, MySQL will update the value, if it is updated in a view, in the base table as well as in its associated views. To illustrate it we are taking the example of table Student_info having the following data −

mysql> Select * from student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| NULL | Ram     | Jhansi     | Computers  |
+------+---------+------------+------------+
4 rows in set (0.00 sec)

Following is the view ‘Info’ that is created on the base of table ‘Student_info’

mysql> Select * from Info;
+------+---------+------------+
| Id   | Name    | Subject    |
+------+---------+------------+
| 101  | YashPal | History    |
| 105  | Gaurav  | Literature |
| 125  | Raman   | Computers  |
| NULL | Ram     | Computers  |
+------+---------+------------+
4 rows in set (0.00 sec)

Now in the following query, we will update the view ‘Info’ −

mysql> Update info set id = 130 where Name = 'Ram';
Query OK, 1 row affected (0.88 sec)

mysql> Select * from Info;
+------+---------+------------+
| Id   | Name    | Subject    |
+------+---------+------------+
| 101  | YashPal | History    |
| 105  | Gaurav  | Literature |
| 125  | Raman   | Computers  |
| 130  | Ram     | Computers  |
+------+---------+------------+
4 rows in set (0.00 sec)

The above result set shows that vies ‘Info’ got updated.

mysql> Select * from student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| 130  | Ram     | Jhansi     | Computers  |
+------+---------+------------+------------+
4 rows in set (0.00 sec)

The above result set shows that the base table also got updated as and when we updated the view named ‘info’

Below was the view named ‘info_less’ created on the base of view ‘info’ and it also got updated as and when we updated the view ‘info’.

mysql> Select * from info_less;
+------+-------+-----------+
| Id   | Name  | Subject   |
+------+-------+-----------+
| 125  | Raman | Computers |
| 130  | Ram   | Computers |
+------+-------+-----------+
2 rows in set (0.00 sec)
raja
Updated on 22-Jun-2020 13:30:40

Advertisements