Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2006
    Posts
    35

    Unanswered: update three details tables?

    I have added a couple hundred new records in a master table and need to know how I can update the three detail tables by creating corresponding records that include the detail table primary key and the foreign key from the master table.

    For one of the master/detail relationships, the effected fields are:
    Master.PKRCID, Master.Notes - need to be copied to:

    Detail.vRCID, Detail.vNotes.

    How do I also create the detail table primary key, Detail.PKVID? That seems to be the issue. Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would open up Sql Server Books Online and read the article on the UPDATE statement focusing on the FROM and JOIN clauses and then I might read the articles on ALTER TABLE focusing on ADD CONSTRAINT and you might want to read about IDENTITY columns too. Then I would buy one of the many Sql Server programming books.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Thrasymachus View Post
    I would open up Sql Server Books Online and read the article on the UPDATE statement focusing on the FROM and JOIN clauses and then I might read the articles on ALTER TABLE focusing on ADD CONSTRAINT and you might want to read about IDENTITY columns too. Then I would buy one of the many Sql Server programming books.
    Interesting concept.

  4. #4
    Join Date
    Dec 2006
    Posts
    35
    Quote Originally Posted by Thrasymachus View Post
    I would open up Sql Server Books Online and read the article on the UPDATE statement focusing on the FROM and JOIN clauses and then I might read the articles on ALTER TABLE focusing on ADD CONSTRAINT and you might want to read about IDENTITY columns too. Then I would buy one of the many Sql Server programming books.
    Thanks, Thrasymachus. I have many books on Sql Server and they are great at gathering dust! The great thing about the many database forums out there and the millions of posts is that a determined amateur like myself can quickly find direct answers to specific problems. The thread on joins from several weeks ago saved me hundreds of hours and put me on the path to learning about joins. I'll take your advice and see what I can learn about ALTER TABLE and ADD CONSTRAINT.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Reading your thread, I think INSERT rather than UPDATE will be more relevant.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2006
    Posts
    35
    Thanks! I'll start there.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by smacky05 View Post
    Thanks, Thrasymachus. I have many books on Sql Server and they are great at gathering dust! The great thing about the many database forums out there and the millions of posts is that a determined amateur like myself can quickly find direct answers to specific problems. The thread on joins from several weeks ago saved me hundreds of hours and put me on the path to learning about joins. I'll take your advice and see what I can learn about ALTER TABLE and ADD CONSTRAINT.
    yes. you will gain just enough knowledge to be dangerous. I hope it is nothing delicate and important.

    there is a difference between cowboying things together and doing things as they should be done because you understand what you are doing.

    from your comments, I can tell those poor books have done nothing but collect dust. Unopened. Everybody with their magic internet these days.

    ride on buckaroo.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Dec 2006
    Posts
    35
    Here is my query and the error message is below. I think the issue is automatically adding the next consecutive record number in the primary key field PKVID in the NotesBAK table.

    INSERT INTO NotesBAK
    (vRCID, vAuthor, vAction)
    SELECT PKRCID, Author, Notes
    FROM RollCalls

    Error message:
    Cannot insert the value NULL into column 'PKVID', table 'NotesBAK'; column does not allow nulls. INSERT fails.

    Thanks for your suggestions.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok... if that's where you think the issue is (I agree) then it would behoove you to learn how "identity" works in SQL Server.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2006
    Posts
    35
    Guess who forgot to set the primary key field identity specification to Yes.

    Problem solved...

Tags for this Thread

Posting Permissions

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