CODEWITHSUNDEEP
sql-server-2008viewdelete-row
Hiral Bavisi
Hiral Bavisi

Reputation: 91

Delete a record from a view and from all its related tables

I am using SQL server Management studio 2008.

I want to delete a single record from one of a view which shows null values in that record for all columns.

I can't get to know,from which table i get this null record.

I checked all the tables which are joined for the view but none of the tables contains null record.

Can anyone help me to delete this null record from my view and from all related tables..?

Because I am using this view in many other pages and it creates error in each page with null value.

when i try to delete this record from view it shows error like

"Msg 4405, Level 16, State 1, Line 1
View or function 'viewGetProgressOverview' is not updatable because the modification affects multiple base tables."

Upvotes: 4

Views: 36806

Answers (4)

Hiral Bavisi
Hiral Bavisi

Reputation: 91

The last step i need to take is to recreate the hole database with my last backup and recreate the tables i created after that backup.. Right now its also solved my problem. Thanks all for your replies and help..

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

If your view definition includes an outer join, the dbms is probably manufacturing those nulls. If your view requires an outer join, there's not really any way around those nulls. If you keep them, well, you'll see nulls. If you hide them, then you're defeating the purpose of the outer join.

The only way to remove them that's consistent with a required outer join is to delete a row from the preserved table. (The row whose key is causing the dbms to manufacture the nulls.)

The only thing that can keep you from identifying the troublesome row in the preserved table is that the view doesn't include any of that table's candidate keys. Add a candidate key from each table, one at a time, until you find the right one. You don't need to change the view definition to do that; just copy the view's SELECT statement to a SQL window.

Upvotes: 0

Ravi
Ravi

Reputation: 31417

If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.

However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:

Msg 4405, Level 16, State 1, Line 1
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.

The rules for Update join views are as follows:

Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.

UPDATE Rule

All updatable columns of a join view must map to columns of a key-preserved table. See "Key-Preserved Tables" for a discussion of key-preserved tables. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.

DELETE Rule

Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.

INSERT Rule

An INSERT statement must not explicitly or implicitly refer to the columns of a nonkey preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.

Reference : -

Inserting to a View – INSTEAD OF TRIGGER – SQL Server

Sql updatable view with joined tables

Upvotes: 7

Andrei Drynov
Andrei Drynov

Reputation: 8592

Can't you just hide the record from the view's results? E.g.

where subscriptionID IS NOT NULL

Upvotes: 1

Related Questions