MSQL: Delete rows from VIEW

Question

Is it possible to delete rows from a View?

Bandoleer

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

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

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

MSQL? Do you mean MS SQL Server? – Outrigger

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

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

Answer 1

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

Craftwork

Answer 2

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

Tachygraphy

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...) – Outrigger

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

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

Answer 3

there! I agree with the answer above. I just wanted to add that for some engines like DB2. It is actually possible to delete a row directly from a view (DELETE FROM my_view WHERE ) and it will automatically delete the row from the source table that the view is based on. So beware of which engine you're working on.

Acerb

You have to set up the view so that it has an instead of trigger to do this. – Shurlocke

You wrote (in your answer): I agree with the answer above Which one? There are two other answers and the order in which they are displayed is not constant. – Corpuscle

Please format your answer, in a better way. – Biased