Delete from Sql View

Home Forums Database Sql Server Delete from Sql View

This topic contains 0 replies, has 2 voices, and was last updated by  Pavan 6 years, 7 months ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #2990

    Ankur
    Moderator

    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

    #2991

    Pavan
    Moderator

    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.

    #2994

    Ankur
    Moderator

    Can you please send me some sql example ?

    #3010

    Ankur
    Moderator

    I am waiting for any response……
    please send

    Thanks

    #3037

    Pavan
    Moderator

    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.

    #3049

    Ankur
    Moderator

    Thank you Pavan
    It help me a lot.

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

    #3050

    Pavan
    Moderator

    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.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.