How can I update (or edit) a View in MySQL database using phpMyAdmin.
I got a view that's made of columns from two tables - I added a new column to one of these, but the view doesn't have it. I can't find the MySQL query I used to get this view (it's quite obscure one) - so how can I edit the MySQL query that created this view to add a new column into it?
In MySQL, views are not only query-able but also updatable. It means that you can use the INSERT or UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.
If you wish to modify table details, select the Operations tab at the top of the screen. 8. Use the Table Options panel to make the required changes to the table. You can edit its name, add comments, and more.
Yes, Views automatically update in MySQL; including, but not limited to: Changing table structures. Insert/Update/Delete procedures on Tables. Changing View structures using CREATE OR REPLACE VIEW.
A Simpler Way
Most of the time, not being able to edit views etc. is due to the DEFINER being set to root@localhost, and if you're coming from a web host control panel, that user is not you.
This has been tested on PHPMyAdmin 4.9.0.1, it may also work on earlier and later versions. PHPMyAdmin seems to remember your username for the rest of the session, so that you can edit freely.
You can also use CREATE OR REPLACE VIEW
, to avoid the step of dropping the view:
show create view viewname
. Find definition in 'Create View' columnCREATE OR REPLACE VIEW
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With