Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to update a view in phpMyAdmin?

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?

like image 947
MarcinWolny Avatar asked Mar 01 '13 11:03

MarcinWolny


People also ask

How do I update a view in MySQL?

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.

How do you update a table in phpMyAdmin?

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.

Do views update automatically MySQL?

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.


2 Answers

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.

  1. Click Home (house icon) top left, copy your username to the right.
  2. Click your view on the left > Structure > Edit View and edit.
  3. Paste your username in place of 'root@localhost' and click Go.

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.

like image 70
Henrik Erlandsson Avatar answered Oct 13 '22 19:10

Henrik Erlandsson


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' column
  • use below command to add new columns: CREATE OR REPLACE VIEW
like image 33
Aris Avatar answered Oct 13 '22 19:10

Aris