Update Statement Not Working

Question

Post reply

Update Statement Not Working

David-155102

SSCarpal Tunnel

Points: 4361
More actions
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

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

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

Try adding

SET NOCOUNT ON

as the first line of the stored procedure.

Regards,
gova

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 2

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.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

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

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 4

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

noeld SSC Guru Points: 96590 More actions · Answer 5

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

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 6

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?

kingram-15027 Ten Centuries Points: 1074 More actions · Answer 7

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

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

exec(@str)

Ruud Boots Old Hand Points: 320 More actions · Answer 8

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

Ruud

SJTerrill SSCrazy Points: 2938 More actions · Answer 9

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-

noeld SSC Guru Points: 96590 More actions · Answer 10

David,

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

* Noel