Post reply

Update Statement Not Working

  • May 31, 2005 at 9:32 am

    #167057

    I have a stored procedure that updates two tables as follows:

     UPDATE Table1 

     SET @Month = NULL

     WHERE unit_id in (SELECT     MEM_ID

       FROM      UNIT_TREE

       WHERE     MEM_PID IN

                               (SELECT     MEM_ID

                                 FROM          UNIT_TREE

                                 WHERE      MEM_PID = @nSubs Or MEM_ID = @nSubs)

       GROUP BY MEM_ID

       UNION ALL

       SELECT @nSubs)

     UPDATE Table2

     SET @Month = NULL

     WHERE unit_id in(SELECT     MEM_ID

       FROM      UNIT_TREE

       WHERE     MEM_PID IN

                               (SELECT     MEM_ID

                                 FROM          UNIT_TREE

                                 WHERE      MEM_PID = @nSubs Or MEM_ID = @nSubs)

       GROUP BY MEM_ID

       UNION ALL

       SELECT @nSubs)

    When the procedure executes it updates the first table as expected but does not alter any rows in the second table. If I run the sql for the second table on its own though, it does update the table. Can anyone help?

    Thanks

  • May 31, 2005 at 9:40 am

    #561932

    Try adding

    SET NOCOUNT ON

    as the first line of the stored procedure.

    Regards,
    gova

  • May 31, 2005 at 10:08 am

    #561947

    It's not so much the message that not working, when I run the sp the second table is not updated (checked by querying the table). When I run the statement on its own the records in the table are updated.

  • May 31, 2005 at 10:22 am

    #561950

    I had this problem once when the stored procedure was executed from front ent the rows effected message stops execution and returned the message.

    Is it happening even when the SP is executed in Quey Analyzer. Then SET NOCOUNT ON will not help. If it works in the back end and only front end call fails SET NOCOUNT ON will help.

    Sorry I did not get the problem well enough.

    Regards,
    gova

  • May 31, 2005 at 10:58 am

    #561962

    Thanks for that but unfortunately the problem is still there. Just for information, I'm running the sp from Query Analyser.

  • May 31, 2005 at 11:05 am

    #561963

    I really don't understand how even the First update is affecting anything in the Table

    UPDATE Table1 

     SET @Month = NULL

     WHERE unit_id ...

     @Month is a variable, Not a Column 

    Same for the Second statement !

     

     


    * Noel

  • June 1, 2005 at 1:12 am

    #562087

    Hi Noel,

    The column name is being specified when the sp is run by giving @Month the appropriate value. Could the problem have anything to do with the sub queries I'm using?

  • June 1, 2005 at 8:07 am

    #562189

    david, i think this is going to have to be dynamic SQL statement.

    set @srt = 'update table1 set ' + @month + ' = null....blah blah...'

    exec(@str)

     

  • June 1, 2005 at 11:25 am

    #562285

    try adding a "go" statement between the two update statements

     

    Ruud

    Ruud

  • June 1, 2005 at 11:30 am

    #562288

    Actually, I think noeld has the right idea. The code is simply updating a variable in memory, not a column in the table itself!

    -SJT-

  • June 1, 2005 at 1:27 pm

    #562337

    David,

    Can you post the code of the entire procedure so that I can understand what are you trying to accomplish?

     

     

     

     

     


    * Noel

Viewing 11 posts - 1 through 10 (of 10 total)

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