With the help of ALTER VIEW statement, we can modify the definition of MySQL view. In this case, we do not need to drop it. The syntax would be as follows −
ALTER VIEW view_name AS SELECT column1,column2… FROM table WHERE conditions;
To illustrate it we are modifying the definition of a view named ‘Info’ which have the following data −
mysql> Select * from Info; +------+---------+------------+ | Id | Name | Subject | +------+---------+------------+ | 101 | YashPal | History | | 105 | Gaurav | Literature | | 125 | Raman | Computers | | 130 | Ram | Computers | +------+---------+------------+ 4 rows in set (0.01 sec)
Now, suppose if we want to add one more column in this view then it can be done with the help of ALTER VIEW statement as follows −
mysql> Alter view info AS SELECT ID, NAME, SUBJECT, ADDRESS from student_info; Query OK, 0 rows affected (0.07 sec) mysql> Select * from info; +------+---------+------------+------------+ | ID | NAME | SUBJECT | ADDRESS | +------+---------+------------+------------+ | 101 | YashPal | History | Amritsar | | 105 | Gaurav | Literature | Chandigarh | | 125 | Raman | Computers | Shimla | | 130 | Ram | Computers | Jhansi | +------+---------+------------+------------+ 4 rows in set (0.00 sec)
The above result set shows that column ADDRESS has been added to the view ‘Info’.