CODEWITHSUNDEEP
sqlsql-serveroracle-databasepostgresql
Avadhesh
Avadhesh

Reputation: 4703

Is a view in the database updatable?

Can you update a view in a database? If so, how? If not, why not?

Upvotes: 19

Views: 22420

Answers (12)

MANISH PRIYADARSHI
MANISH PRIYADARSHI

Reputation: 121

We generally don't update a view. A view is written to fetch data from the various tables based on joins and where conditions put.

View is just a logic put in place which gives the desired data set on invoking it.

But not sure on what scenario one needs to update a view.

Upvotes: 0

Ishara
Ishara

Reputation: 81

Yes, they are updatable but not always. Views can be updated under followings:

  • If the view consists of the primary key of the table based on which the view has been created.

  • If the view is defined based on one and only one table.

  • If the view has not been defined using groups and aggregate functions.
  • If the view does not have any distinct clause in its definition.
  • If the view that is supposed to be updated is based on another view, the later should be updatable.
  • If the definition of the view does not have any sub queries.

Upvotes: 5

Shashi
Shashi

Reputation: 11

When a view is created in SQL Server, metadata for the referenced table columns (column name and ordinal position) is persisted in the database. Any change to the referenced base table(s) (column re-ordering, new column addition, etc) will not be reflected in the view until the view is either:

•Altered with an ALTER VIEW statement •Recreated with DROP VIEW/CREATE VIEW statements •Refreshed using system stored procedure sp_refreshview

Upvotes: 1

Murph
Murph

Reputation: 10190

The actual answer is "it depends", there are no absolutes.

The basic criteria is it has to be an updateable view in the opinion of the database engine, that is to say can the engine uniquely identify the row(s) to be updated and secondly are the fields updateable. If your view has a calculated field or represents the product of a parent/child join then the default answer is probably no.

However its also possible to cheat... in MS SQL Server and Oracle (to take just two examples) you can have triggers that fire when you attempt to insert or update a view such that you can make something that the server doesn't think updateable into something that is - usually because you have knowledge that the server can't easily infer from the schema.

Upvotes: 20

Tegiri Nenashi
Tegiri Nenashi

Reputation: 3086

There are two approaches:

  1. INSTEAD OF trigger, which basically shifts the problem to the user. You write some procedural code that does the job. Certainly, no guarantees is made about correctness, consistency, etc. From RDBMS engine perspective a trigger that deletes everything from the base tables, no matter what update is made in the view, is perfectly fine.

  2. Much more ambitious is view updates handled exclusively by RDBMS engine. Not much progress is made here: to put it mildly, if you have some good ideas there, then you can roll out PhD thesis. In practice, your favorite RDBMS might allow some limiting ad-hock view updates; check the manual:-)

Upvotes: 2

Gaius
Gaius

Reputation: 2595

Yes, using an INSTEAD OF trigger.

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

In the past it wasn't possible to update any views. The main purpose of a view is to look at data, hence the name. It could also have been called a stored query.

Today, many database engines support to update views. It's bound to restrictions, some updates are virtually impossible (eg. calculated columns, group by etc).

Upvotes: 3

Frank Heikens
Frank Heikens

Reputation: 127136

PostgreSQL has RULEs to create updatable VIEWs. Check the examples in the manual to see how to use them.

Ps. In PostgreSQL a VIEW is a RULE, a select rule.

Upvotes: 5

Lord Peter
Lord Peter

Reputation: 3501

The correct answer is "it depends". You can't update an aggregate column in a view for example. For Oracle views you can Google for "updatable join view" for some examples of when you can and cannot update a view.

Upvotes: 4

codingbadger
codingbadger

Reputation: 43994

Yes they are - the syntax is the same as updating a table

Update MyView
Set Col1 = "Testing"
Where Col2 = 3
Go

There a few conditions to creating an View that can be updated. They can be found here

EDIT:

I must add that is based on MS SQL

Upvotes: 1

gandjustas
gandjustas

Reputation: 1955

http://msdn.microsoft.com/en-us/library/ms187956.aspx

See Remarks\updateable view

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166406

Yes you can, but have a look at CREATE VIEW (Transact-SQL) and see the section Updatable Views

Upvotes: 1

Related Questions