4

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."
Ravi
  • 30,829
  • 42
  • 119
  • 173
Hiral Bavisi
  • 91
  • 1
  • 3
  • 10
  • Hmm, sql server is right, a view is "normally" readonly (If your update changes only data from one table, it works). I'm sorry, but you have to show off your view-definition (and maybe the definition of the underlying tables) – pbhd Dec 25 '12 at 12:02
  • delete from viewGetProgressOverview where subscriptionID is null....I am trying to run this query.. – Hiral Bavisi Dec 25 '12 at 12:03
  • @HiralBavisi not DML, your DDL statement for view – Ravi Dec 25 '12 at 12:05
  • possible related problem, http://stackoverflow.com/questions/7281054/sql-updatable-view-with-joined-tables – spajce Dec 25 '12 at 12:05
  • @pbhd and var___ I cant paste the query because it is too long.. – Hiral Bavisi Dec 25 '12 at 12:05
  • in your question. Edit your question and paste your view DDL. – Ravi Dec 25 '12 at 12:06
  • until you won't paste your view DDL, we can't get the exact problem. Nevertheless, you can follow this http://stackoverflow.com/questions/6267985/unable-to-perform-delete-on-view-sql-server-2005 – Ravi Dec 25 '12 at 12:10

4 Answers4

7

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

Community
  • 1
  • 1
Ravi
  • 30,829
  • 42
  • 119
  • 173
  • @HiralBavisi In that case either use stored procedure or trigger. For more, you can refer 1st link. And, if you still have any problem, let me know. – Ravi Dec 25 '12 at 12:27
  • @HiralBavisi tell me one thing, what type of join query have you used for view (i mean outer or inner) ? – Ravi Dec 25 '12 at 12:37
1

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

where subscriptionID IS NOT NULL
Andrei Drynov
  • 8,362
  • 6
  • 39
  • 39
  • this is not the solution, but OP would be satisfied with this, then ok. – Ravi Dec 25 '12 at 12:11
  • @ADNow I dont know it will help me or not but can you just tell me how to hide a single row..I should try once.. – Hiral Bavisi Dec 25 '12 at 12:13
  • If possible, share the view query with us, obfuscate it if necessary. And personally, I think updateble views defeat the purpose of views (good discussion here too: http://stackoverflow.com/questions/214132/what-are-views-good-for), views are good for more secure read-only access to the data. – Andrei Drynov Dec 25 '12 at 12:15
0

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
0

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..

Hiral Bavisi
  • 91
  • 1
  • 3
  • 10