
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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’.
- Related Articles
- How can we get the definition of a MySQL view as we can get the definition of a MySQL table?
- How can we modify a MySQL view with CREATE OR REPLACE VIEW statement?
- How can we modify column/s of MySQL table?
- How can we modify an existing MySQL event?
- How can we get the structure of a MySQL view as we can get the structure of a MySQL table?
- How can we drop a MySQL view from the database?
- How can we list all the columns of a MySQL view as we can list the columns of a MySQL table?
- How can we create a MySQL view based on another existing view?
- How can we create a MySQL view with a subquery?
- Dropping a SAP HANA database view
- How can we create a MySQL view with LEFT JOIN?
- How can we create a MySQL view with INNER JOIN?
- How can we create a MySQL view with RIGHT JOIN?
- How can we create a MySQL view with GROUP BY clause?
- How can we update any value in MySQL view as we can update the values in MySQL table?

Advertisements