Post reply

Updating Union Views

  • July 11, 2005 at 11:20 pm

    #86846

    Hi,

    I have 3 databases. General, Utility & ClientUtilty. Utility & ClientUtlity have a table Dept (deptid int , deptname varchar(20))

    There is one view Dept in General:

    Create view Dept as

    select * from Utlity.Dbo.Dept

    Union

    select * from ClientUtlity.Dbo.Dept

    Now I want to Update the Dept VIEW in General Database...

    Can anyone help me

    Kindest Regards,

    R

  • July 12, 2005 at 2:36 am

    #573268

    how about drop view --> re-create view ?

  • July 12, 2005 at 3:45 am

    #573286

    No, I want to insert/update/delete records in the view.

    I have done it using three instead of triggers.

    Thanks.

    Kindest Regards,

    R

  • July 12, 2005 at 8:34 am

    #573394

    I can't get u prakash.. can v inse..,dele.. ,upda.. in a view.. Is it possible...

  • July 12, 2005 at 11:34 pm

    #573764

    There are so many rules bound to views for update/insert/delete!!!!

    one rule states UPDATE statement cannot affect more than one of the base tables referenced in the FROM clause of the view.

    Instead of triggers are cool, why don't you continue using that?

  • July 21, 2005 at 7:58 pm

    #576288

    I may be totally off-base on this, and I can't find anything in BOL to back me up, but it was my understanding that UNION views are SELECT or INSERT FROM only, that you couldn't UPDATE or DELETE from them.

    Also, doing SELECT * is risky in a couple of ways. First, it's a performance hit, I would think it would be even moreso in a union query. Second, you're introducing a complication for updates. When you're explicit in specifying your fields, if you modify one table and add a field, you go and modify the view and see that additional tables need to reflect the change one way or another.

    But like I said, I may be totally off-base. I use UNIONs a lot (extremely useful tool), but I've never tried updating them.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply