Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Update Stored procedure

    I need to create a stored procedure that will update, an insert will try to insert an entire row and I am only trying to update old data with new data. For instance if I move all the 99 terms from the active table to the term table and lets say for example their [hiredate], [ID], [firstname], but after the update is done I realize I forgot to include the [lastname] field, see what I mean??? Or I just wanted to UPDATE old data with new data?? Would this stored procedure work

    Code:
    CREATE PROCEDURE [InsertTerms]
    AS
    INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] 
    (ReasonTerminated)
         
    SELECT a.DESCRIPTION
    FROM  DesireeTerm3
    WHERE TERIMINATION.TM#= DesireeTerm3.Employee
    RETURN
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by desireemm
    ... see what I mean???
    Actually, no....

    Why don't you just use an UPDATE statement?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Yes your right

    an insert will insert the entire row, hows this???? Theres about 537 fields from the Termination table (Reasonfortermination) thats null and the info I need is in the DesireeTerm3 table (DESCRIPTION)


    CREATE PROCEDURE p_UpdateTerms
    (@EmpID int)
    ASDeclare @Reason varchar(30)Select @Reason = Select [Description] From DesireeTerm3 Where TERMINATION.[TM#] = @EmpIDUPDATE [GamingCommissiondb].[dbo].[TERMINATION]SET ReasonTerminated = @Reason
    Last edited by desireemm; 01-23-06 at 12:18.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is there a common field between TERMINATION and DesiereeTerm3 that you can join on? EmpID, perhaps? If so, your update statement is as simple as this:
    Code:
    update	Termination
    set	ReasonTerminated = DesireeTerm3.[Description]
    from	[GamingCommissiondb].[dbo].[TERMINATION] Termination
    	inner join DesireeTerm3 on Termination.[TM#] = DesireeTerm3.EmpID
    where	Termination.[TM#] = @EmpID
    If not, please push your chair back from your desk, take a few breaths, go have a cup of coffee (or Chai Tea, if that is what Californians drink), and when you are ready come back and describe your problem clearly and completely.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ok

    All I want to do is update one field in the [TERMINATION].[Reasonfortermination]with the data from another table [DesireeTerm3].[DESCRIPTION]. but I keep getting a primary key violation error.
    The primary key in the DesireeTerm3 table is Employee (INT)
    and in the TERMINATION TM # (INT)
    THE field I am trying to update is [Reasonfortermination] which is in the TERMINATION.tbl, with data from [DesireeTerm3].[DESCRIPTION] the data for both those fields is NVARCHAR (255)
    Code:
    CREATE PROCEDURE p_UpdateTerms
    (
    @EmpID int
    )
    
    AS
    
    Declare @Reason nvarchar(255)
    
    Select @Reason = [DESCRIPTION] From DesireeTerm3 Where TERMINATION.[TM #] = @EmpID
    
    UPDATE [Test].[dbo].[TERMINATION]
    SET Reasonfortermination = @Reason

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The two tables have to be linked by a foreign key.

    Does [TM #] exist in DesireeTerm3?

    Does [Employee] in TERMINATION?

    Without some common field between these two tables, you cannot do your update.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Thanks Anyways blindman

    I just didnt want to enter in all 537 fields with the data I inherited from a different database (IT gave it to me) I guess I'll have to just manually type the information in. I kept thinking there has to be a faster way of doing this. I'll just print out the DESCRIPTIONS from the DesireeTerm3 table and type it into the TERMINATIONS Reasonfortermination field

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK. One last try to help you see the light:

    HOW are you going to tell WHICH of the descriptions goes into each Reasonfortermination field?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    NO your right

    No your right I see your point just getting frustrated thats all sorry

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Hooorraaayyyyy

    It Worked It Worked

    Thank You So Much Brett Thank You Thank You Thank You


    Woohooooooo!!!!!!!!!!!!!!!!!!!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Brett ?!?!

    -PatP

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by desireemm
    It Worked It Worked

    Thank You So Much Brett Thank You Thank You Thank You
    So Brett has cloned himself and is now masquerading as blindman?

    Be afraid, be very afraid!!!



    hmscott


    Edit to add: Pat beat me to it!!!
    Have you hugged your backup today?

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking oops

    sorry I meant Blindman

    sorry My bad I got caught up in the EXCITEMENT

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that Blindman is becomming a meta-personality for us all! First Pootle-Flump thinks he's me in another thread. Now DesireeM thinks he's Brett... Pretty quick he'll just become the over-arching personality that consumes us all!

    -PatP

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Kropes' nightmare!!!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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