Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Post Unanswered: UPDATE statement

    Hi All,

    I am trying to update a table with a value from another table. I can execute the stored procedure successfully but the update is not taking place. Below is the code which I tried.

    ALTER PROCEDURE [dbo].[sp_Update]
    (
    @DrawingNo nvarchar(30)
    )
    AS
    BEGIN

    UPDATE dbo.Drawings
    SET dbo.Drawings.Revision = dbo.DrawingsVersions.Revision
    FROM dbo.Drawings
    INNER JOIN
    dbo.DrawingsVersions ON
    dbo.Drawings.Revision = dbo.DrawingsVersions.Revision
    WHERE (dbo.Drawings.DrawingNo = @DrawingNo)

    END



    I am trying to update the revision number of a drawing in the “Drawings” table with the revision number of the same drawing in the “DrawingsVersions” table by passing the drawing number as a parameter. Can anyone help please.

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What does this return?
    Code:
    ALTER PROCEDURE [dbo].[sp_Update]
    (
    	@DrawingNo nvarchar(30)
    )
    AS
    BEGIN
    	
    SELECT dbo.Drawings.Revision, dbo.DrawingsVersions.Revision
    FROM dbo.Drawings 
    INNER JOIN
    		dbo.DrawingsVersions ON
    		dbo.Drawings.Revision = dbo.DrawingsVersions.Revision
    WHERE (dbo.Drawings.DrawingNo = @DrawingNo)
    
    END
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    return value is "0" and says that 1 row(s) affected

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is the data that is displayed?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    why?

    Drop Revision from the drawing table

    It already exists in the revision table

    The data will be stale as soon as you run the update
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    Hi,

    Thanks for the swift reply. "what is the data that is displayed?" . Could you please make this question clear?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The procedure includes a select statement. It returns a dataset. What is the data in that dataset?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    Hi,

    I am pretty new to SQL server. I dont get anything in return. All i can see is the message saying 1 row(s) updated after passing the parameter value

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.
    Running the code I posted will alter the stored procedure. You need to execute the stored procedure to actually run the code it contains. I suspect this is why your update never worked in the first place.

    I would recommend that you go to your boss and ask to get on an introductory SQL Server course, or get some study time to work through some of the basics.

    Also, since we now know you are new to the field - Brett is correct and it looks like the update you are attempting to perform is ill advised and, very probably, violates good database design principles. To learn more about these principles please read this link:
    The Relational Data Model, Normalisation and effective Database Design
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh hang on - I misread your post - you are executing the procedure. Did you run my code *first*?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2009
    Posts
    7
    i cant see the code

  12. #12
    Join Date
    Jul 2009
    Posts
    7
    sorry i executed your code. i get a message saying 5569 rows updated. And a table with two columns named "Revisions" having "0" as value in all the fields

  13. #13
    Join Date
    Jul 2009
    Posts
    7
    Hi i am able to update the revision number. i jus changed the innerjoin (previously i joined dbo.Drawings.RevisionNo = dbo.DrawingsVersions.RevisionNo). I changed that to dbo.Drawings.DrawingNo = dbo.DrawingsVersions.DrawingNo and executed it. The revision number in the "Drawings" table was updated with the revision number for "DrawingsRevision" table when the drawing number was passed as the parameter.
    This is the code which gave the result

    ALTER PROCEDURE [dbo].[sp_Update]
    (
    @DrawingNo nvarchar(30)
    )
    AS
    BEGIN

    UPDATE dbo.Drawings
    SET dbo.Drawings.Revision = dbo.DrawingsVersions.Revision
    FROM dbo.Drawings
    INNER JOIN dbo.DrawingsVersions ON
    dbo.Drawings.DrawingNo = dbo.DrawingsVersions.DrawingNo
    WHERE (dbo.DrawingsVersions.DrawingNo = @DrawingNo)

    END

    Anyways thank you verymuch for your help. Appreciate it. Take care

Posting Permissions

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