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

MySQLMySQLi Database

<p style="">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 &minus;</p><h2 style="">Syntax</h2><pre class="prettyprint notranslate">ALTER VIEW view_name AS SELECT column1,column2&hellip; FROM table WHERE conditions;</pre><h2 style="">Example</h2><p style="">To illustrate it we are modifying the definition of a view named &lsquo;Info&rsquo; which have the following data &minus;</p><pre class="prettyprint notranslate">mysql&gt; Select * from Info; +------+---------+------------+ | Id &nbsp; | Name &nbsp; &nbsp;| Subject &nbsp; &nbsp;| +------+---------+------------+ | 101 &nbsp;| YashPal | History &nbsp; &nbsp;| | 105 &nbsp;| Gaurav &nbsp;| Literature | | 125 &nbsp;| Raman &nbsp; | Computers &nbsp;| | 130 &nbsp;| Ram &nbsp; &nbsp; | Computers &nbsp;| +------+---------+------------+ 4 rows in set (0.01 sec)</pre><p style="">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 &minus;</p><pre class="prettyprint notranslate">mysql&gt; Alter view info AS SELECT ID, NAME, SUBJECT, ADDRESS from student_info; Query OK, 0 rows affected (0.07 sec) mysql&gt; Select * from info; +------+---------+------------+------------+ | ID &nbsp; | NAME &nbsp; &nbsp;| SUBJECT &nbsp; &nbsp;| ADDRESS &nbsp; &nbsp;| +------+---------+------------+------------+ | 101 &nbsp;| YashPal | History &nbsp; &nbsp;| Amritsar &nbsp; | | 105 &nbsp;| Gaurav &nbsp;| Literature | Chandigarh | | 125 &nbsp;| Raman &nbsp; | Computers &nbsp;| Shimla &nbsp; &nbsp; | | 130 &nbsp;| Ram &nbsp; &nbsp; | Computers &nbsp;| Jhansi &nbsp; &nbsp; | +------+---------+------------+------------+ 4 rows in set (0.00 sec)</pre><p>The above result set shows that column ADDRESS has been added to the view &lsquo;Info&rsquo;.</p>
raja
Updated on 22-Jun-2020 13:31:43

Advertisements