How can we modify the definition of a MySQL view without dropping it?

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 −

Syntax

ALTER VIEW view_name AS
SELECT column1,column2…
FROM table
WHERE conditions;

Example

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’.