Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: Updating a View

  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Updating a View

    Hi,
    In what way a view can be updated / inserted / deleted (records)? Kinldy make me clear on this. Have gone through lot many blogs / articles and got more and more confused.




    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Broadly two ways.
    1) The view is directly updateable (i.e. it is reasonably simple and the engine can work out what to do to the base table if you perform any DML actions on it).
    2) The view is not directly updateable (i.e. it is not reasonably simple and the engine can not work out what to do to the base table if you perform any DML actions on it). In this instance you need to write instead of triggers and handle the logic for the underlying tables yourself.
    There might be more to it but I've only ever really dipped my toe into the waters of updating views in the past.

    If that is no help please be more specific about what you are struggling with.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Quote Originally Posted by pootle flump
    Broadly two ways.
    1) The view is directly updateable (i.e. it is reasonably simple and the engine can work out what to do to the base table if you perform any DML actions on it).
    2) The view is not directly updateable (i.e. it is not reasonably simple and the engine can not work out what to do to the base table if you perform any DML actions on it). In this instance you need to write instead of triggers and handle the logic for the underlying tables yourself.
    There might be more to it but I've only ever really dipped my toe into the waters of updating views in the past.

    If that is no help please be more specific about what you are struggling with.
    thnkx for ur reply. but i am not getting what acuatlly you meant by base table. and both the scenarios looks the same for me. kinldy elaborate......



    thnkx
    rahul jha

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just ignore the base bit. Table.

    Seriously - you didn't spot the difference in the post (a view that is updateable vs a view that is not updateable)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    M sorry again, but not getting which two different kind of view you are talking about.......

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I was actually a bit worried that I had dumbed my first post down a little too much.
    Perhaps a better way would be for you to tell us what you have read and understood so far from all your research and we can try to fill in the gaps.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    that so nice, but as i said earlier that i got confused myself while going thru multiple os blogs......... hence came to this forum......

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You understood nothing at all? You did not draw a single thing from any article?

    Ok - last try - what do you see as your problem? Why are you asking the question? For example - have you tried to insert into a view and had an error?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    no, haven't tried yet. but theoritically wanted to know......... that whether a view can be updtaed / inserted / deleted (records) or not?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DBA_Rahul
    that whether a view can be updtaed / inserted / deleted (records) or not?
    Aha!

    Yes it can
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    :-) now my next question............ can this be done to all the tables been included in view or just the one table?

  12. #12
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    no reply yet..............

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why not just try it and see?
    Code:
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type='u' and name='table1') BEGIN
        DROP TABLE table1
    END
    
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type='u' and name='table2') BEGIN
        DROP TABLE table2
    END
    
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type='v' and name='view1') BEGIN
        DROP VIEW view1
    END
    
    CREATE TABLE table1 (
          field1 int
        , field2 int
        , field3 int
        , fieldn int
      )
    
    CREATE TABLE table2 (
          field01 int
        , field02 int
        , field03 int
        , field0n int
      )
    
    SET NOCOUNT ON
    
    INSERT INTO table1(field1, field2, field3, fieldn)
    SELECT 1,1,1,1 UNION ALL
    SELECT 2,2,2,2 UNION ALL
    SELECT 3,3,3,3 UNION ALL
    SELECT 4,4,4,4 UNION ALL
    SELECT 5,5,5,5
    
    INSERT INTO table2(field01, field02, field03, field0n)
    SELECT 1,1,1,1 UNION ALL
    SELECT 2,2,2,2 UNION ALL
    SELECT 3,3,3,3 UNION ALL
    SELECT 4,4,4,4 UNION ALL
    SELECT 5,5,5,5
    
    SET NOCOUNT OFF
    GO
    
    CREATE VIEW view1 AS
    SELECT t1.field1  As [a]
         , t1.field2  As [b]
         , t1.field3  As [c]
         , t2.field01 As [d]
         , t2.field02 As [e]
         , t2.field03 As [f]
    FROM   table1 As t1
     LEFT
      JOIN table2 As t2
        ON t1.fieldn = t2.field0n
    GO
    
    SELECT * FROM view1
    
    UPDATE view1
    SET a = 11
      , b = 11
      , c = 11
      , d = 11
      , e = 11
      , f = 11
    WHERE a = 1
    GO
    
    SELECT * FROM view1
    
    DROP VIEW  view1
    DROP TABLE table1
    DROP TABLE table2
    The result of the above is not comprehensive.
    How hard would it be for you to just test it yourself?
    George
    Home | Blog

  14. #14
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    thnkx for ur reply, but i got the following error while executing the query given by you.


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



    Thanks,
    Rahul Jha

  15. #15
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    but if i try to update the data of only one table say t1, then its working fine.
    Code:
    USE [dbEDD_TEMP]
    GO
    
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type='u' and name='table1') BEGIN
        DROP TABLE table1
    END
    
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type='u' and name='table2') BEGIN
        DROP TABLE table2
    END
    
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type='v' and name='view1') BEGIN
        DROP VIEW view1
    END
    
    CREATE TABLE table1 (
          field1 int
        , field2 int
        , field3 int
        , fieldn int
      )
    
    CREATE TABLE table2 (
          field01 int
        , field02 int
        , field03 int
        , field0n int
      )
    
    SET NOCOUNT ON
    
    INSERT INTO table1(field1, field2, field3, fieldn)
    SELECT 1,1,1,1 UNION ALL
    SELECT 2,2,2,2 UNION ALL
    SELECT 3,3,3,3 UNION ALL
    SELECT 4,4,4,4 UNION ALL
    SELECT 5,5,5,5
    
    INSERT INTO table2(field01, field02, field03, field0n)
    SELECT 1,1,1,1 UNION ALL
    SELECT 2,2,2,2 UNION ALL
    SELECT 3,3,3,3 UNION ALL
    SELECT 4,4,4,4 UNION ALL
    SELECT 5,5,5,5
    
    SET NOCOUNT OFF
    GO
    
    CREATE VIEW view1 AS
    SELECT t1.field1  As [a]
         , t1.field2  As [b]
         , t1.field3  As [c]
         , t2.field01 As [d]
         , t2.field02 As [e]
         , t2.field03 As [f]
    FROM   table1 As t1
    LEFT JOIN table2 As t2
    ON 
    	t1.fieldn = t2.field0n
    GO
    
    SELECT * FROM view1
    
    UPDATE view1
    SET a = 11
      , b = 11
      , c = 11 /*
      , d = 11
      , e = 11
      , f = 11 */
    WHERE a = 1
    GO
    
    SELECT * FROM view1
    
    DROP VIEW  view1
    DROP TABLE table1
    DROP TABLE table2
    here i have commented the line that updates the tabel t2.

    note: you comments please.


    thnkx,
    rahul jha
    Last edited by gvee; 09-10-07 at 06:08. Reason: added [CODE] tags for readability

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •