MSQL: Delete rows from VIEW

Question

Is it possible to delete rows from a View?

Solution 1

Usually, a view isn't something you can delete from; it's kind of a virtual table, which shows you the rows from one or more real tables in the database. If you want a row to disappear from a view, you need to either delete the data from the real tables behind the view, or alter the view-creating SQL so that that particular row won't be shown in the view. With some simpler views you can DELETE FROM (and update) a view; however, even so the data is actually deleted from the real table.

You also cannot generally add anything to a view; if you need completely new data, it has to be added in the real table(s) from which the view is created.

For view basics, see for example http://www.w3schools.com/sql/sql_view.asp

Solution 2

If your view is updatable - really depends on a database you are using and the way view was created. General rule (again, varies from one DB to another) there should be one table and no aggregates in the select statement, creating the view.

Here is details for MySQL: http://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

And for SQL Server: https://msdn.microsoft.com/en-CA/library/ms187956.aspx

InterSystems Caché: http://docs.intersystems.com/cache20152/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_views#GSQL_views_update

Comments

[jarlh]

Views don't contain data, tables do. You can perhaps do DELETE FROM viewname.

[bpesunny]

The probles is that it deletes the view, and then by insert it creates a table instead of a view.

[jarlh]

DELETE doesn't drop any view... DROP VIEW drops a view!

[jarlh]

MSQL? Do you mean MS SQL Server?

[a_horse_with_no_name]

insert doesn't create new tables, it creates new rows in an existing table.

[Revious]

Possible duplicate of Does deleting row from view delete row from base table - MySQL?

[jarlh]

Some views are updatable, e.g. "select * from base_table". So INSERT, UPDATE and DELETE can be used on the view (and the base table's data is changed...)

[Juha K]

Thanks, I wasn't aware of that! I'll change my answer to reflect this.

[a_horse_with_no_name]

You can even make views with a join updateable using instead of triggers