Post reply

Newbie SQL view question

  • February 28, 2004 at 1:26 pm

    #84305

    Newbie question: I have a client table (ClientID), clientPhone table (ClientID, PhoneID), and Phone table (PhoneID).

    I made a view linking the clientPhone table to the phone table, but can't add any new records! PhoneID in the phone table is an identity column.

    What I need is a table with ClientID, PhoneID, Phonenumber, etc. that I can edit.

    Here is the script from my view:

    SELECT     pph.ClientID, pph.PhoneID AS PPhoneID, ph.*

    FROM         dbo.ClientPhone pph INNER JOIN

                     dbo.Phone ph ON pph.PhoneID = ph.PhoneID

    I greatly appreciate the help!

    SMK

  • February 28, 2004 at 1:44 pm

    #496346

    As you already have found out, not all views in a RDBMS are updatable. SQL Server is not Access.

    You might want to use a stored procedure for updating multiple tables

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 3, 2004 at 4:16 pm

    #497124

    I agree with Frank, functionally you could use an instead of trigger off the view, but I personnally don't like that option.

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • March 3, 2004 at 5:54 pm

    #497142

    Thanks!

    I think I am getting there. Meaning just about to the point where I could write a stored procedure. Life is so much easier when you know what you're doing.

    I don't think I yet qualify for grasshopper.

    SMK

  • March 4, 2004 at 12:40 am

    #497162

    I don't think I yet qualify for grasshopper.

    What would you suggest instead?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 4, 2004 at 5:45 am

    #497216

    Perhaps there could be a category for "those who have lost their minds completely" trying to do 5 jobs at once while picking up a "little" knowledge of SQL technology.

    SMK

  • March 4, 2004 at 6:10 am

    #497227

    In other words 'Guru'

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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