Wednesday, March 7, 2012

returning the primary key of the last rows updated

I would like to return the the Primary Key of the row altered from this query - what changes do I need to make?
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;

Thanks in advance,
Is the primary key an IDENTITY value? If so then you could do this:
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;SELECT @.myPrimaryKey = SCOPE_IDENTITY()

|||I am trying to kill 2 birds with uno piedroSmile [:)] - I want thisreturn the Primary key ( which is the identity ) when someone islogging in and also update the last time they last logged in to thepresent.
My first quess was to do:
PDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;SELECT SCOPE_IDENTITY()
But this keeps returning (NULL) - even though the row has been updated.
Thanks in advance

|||Can you post more of your code?
|||
Actually - this is what I was putting in my query analyzer-
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;
its ok -
I will just work with using this :
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass; SELECT MemberID WHERE UserName=@.user AND Password=@.pass;
I will just dig around some more.


|||Oh gosh, I just realized that you are doing an UPDATE, not an INSERT! I don't know where my head was; I'm sorry.
In that case I would SELECT the MemeberID to be UPDATEd first into avariable, then perform the UPDATE with the WHERE condition being theMemberID you determined.

|||How about using an output parameter and assigning it SCOPE_IDENTITY or @.@.IDENTITY?
I'm not sure if @.@.IDENTITY only gets set to the ID of the latest INSERT or also UPDATE. It would be easy to try of course.

No comments:

Post a Comment