Delete from Sql View

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • November 9, 2011 at 11:47 am #2990
    Ankur
    Member

    Can we delete record using Sql View? Actually we have a large number of records in my table so we need view to delete some record in stead of direct query on table.

    Please advise..

    Thanks

    November 9, 2011 at 1:47 pm #2991
    Pavan
    Member

    Hello Ankur,
    View in sql server conation the data from parent table. View is only used for security mechanism. We can perform delete operation on views but changes on view reflect on parent table also. There is One-to -one relationship between the rows in the view and parent table.
    Like if we create a view V1 from table T1.if we delete the record fromV1 then that record also deleted from table T1.

    November 10, 2011 at 6:51 am #2994
    Ankur
    Member

    Can you please send me some sql example ?

    November 11, 2011 at 6:50 am #3010
    Ankur
    Member

    I am waiting for any response……
    please send

    Thanks

    November 15, 2011 at 11:21 am #3037
    Pavan
    Member

    Hello Ankur,
    i am trying to explain you delete operation on view by example.Like we have a table test have colums name(ID,NAME,SALARY) and now insert some test data into table.Now time to create view Test_view on table test.
    create view Test_view as select * from Test
    Now time to fire delete query on view.
    delete from Test_view where Id=”

    It’s time to check the records view and table both.then you will see the deleted record from view also deleted from table.

    November 18, 2011 at 8:07 am #3049
    Ankur
    Member

    Thank you Pavan
    It help me a lot.

    Can we delete record from view which we create from more then one table ?

    November 18, 2011 at 8:21 am #3050
    Pavan
    Member

    Hello Ankur,
    Thanks for your reply.
    We can’t delete records from view which is created by more than one table.its show you a error message like this :-

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

  • Author
    Posts
Viewing 7 posts - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.