Updating a view sql server
The column names in an expanded column list are enclosed in quote marks to account for the possibility that the columns of the base object were originally entered with quotes and require them for the query to be syntactically correct.As an example, assume that the statement, the database can create the view even if the defining query of the view cannot be executed.When ever you update the records it updates the original data since they are pointers to the original.Suppose you have a table named Course(Course ID, Name, …) in Oracle SQL[Just for your reference might not work in all products] you can do this Normally the above update with out where clause should update all the records but it updates only those records obtained through the query.In this case, the view is considered "created with errors." For example, when a view is created that refers to a nonexistent table or an invalid column of an existing table, or when the view owner does not have the required privileges, the view can be created anyway and entered into the data dictionary. To create a view with errors, you must include the .
Yes, when you update a view it affects original table, because SELECT opens an implicit cursor which points to the records from the base table.
This is possible because of the implicit cursor to the actual data.
Similar is the case with views, but complexity increases with join views.
SELECT COLUMN_NAME, UPDATABLE FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT'; COLUMN_NAME UPD ------------------------------ --- EMPNO YES ENAME YES DEPTNO YES SAL YES DNAME NO LOC NO 6 rows selected.
statement lets you locate recompilation errors before run time.