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

With the ALTER VIEW statement, we can modify the definition of the MySQL view. In this case we don't need to delete it. The syntax is as follows -

Syntax

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

Example

To illustrate this, we are modifying the definition of a view named "Info" which has 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, we can do it with the help of ALTER VIEW statement as shown below-

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 the column ADDRESS has been added to the view "Info".

The above is the detailed content of How can we modify the definition of a MySQL view without deleting it?. For more information, please follow other related articles on the PHP Chinese website!