1
CREATE PROCEDURE sp_updates
   (@fname VARCHAR(100) = NULL,
    @mname VARCHAR(100) = NULL,
    @lname VARCHAR(100) = NULL,
    @sex CHAR = NULL,
    @BirthDate DATE = NULL,
    @phonenumber VARCHAR(12) = NULL,
    @MailId VARCHAR(150) = NULL,
    @City VARCHAR(100) = NULL,
    @State VARCHAR(100) = NULL,
    @Zip VARCHAR(10) = NULL,
    @Line VARCHAR(300) = NULL,
    @PersonID INT)
AS
BEGIN
    UPDATE PERSON 
    SET fname = ISNULL(@fname, fname), 
        mname = ISNULL(@mname, mname), 
        lname = ISNULL(@lname, lname),
        sex = ISNULL(@sex, sex),
        BirthDate = ISNULL(@BirthDate, BirthDate)
    WHERE PersonID = @PersonID

    UPDATE TOP(1) PHONENO
    SET phonenumber = ISNULL(@phonenumber, phonenumber)
    WHERE pno_PersonID = @PersonID

    UPDATE TOP(1) EMAIL
    SET MailId = ISNULL(@MailId, MailId)
    WHERE mail_PersonID = @PersonID

    UPDATE TOP(1) ADDRESS
    SET City = ISNULL(@City, City),
        State = ISNULL(@State, State),
        Zip = ISNULL(@Zip, Zip)
    WHERE add_PersonID = @PersonID

    UPDATE TOP(1) LINE
    SET Line = ISNULL(@Line, Line)
    WHERE l_PersonID = @PersonID
END

Calling it:

EXEC sp_updates @fname = gagan, @mname = NULL , @lname = NULL, 
@sex=NULL,@BirthDate='1992-02-19',@phonenumber='1234-234- 
345',@MailId='updates@gmail.com',@City='updated',@State='updated',
@Zip='updated',@Line='updated',@PersonID=1;

SELECT TOP(1) * FROM EMAIL WHERE mail_PersonID=1;

I want to update only one TUPLE (in SQL Server) and then I want to select the same tuple I updated. From BELOW picture, I want to update only first phone number 165-398-9385 to '123-567-5678' for person id = 1. and then i want to get only that phone number which i recently updated for person id =1.

I tried to use TOP(1) but it's not working. It returns a result different from the original tuple I updated. 

SELECT TOP(1) * 
FROM EMAIL 
WHERE mail_PersonID = 1;

returns another tuple which was not updated.

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gagandeep Singh
  • 19
  • 1
  • 9
  • 1
    Please post the code you have tried as part of your post. – Ryan Wilson Oct 24 '18 at 16:34
  • Possible duplicate of [Getting the Id of a row I updated in Sql Server](https://stackoverflow.com/questions/1610509/getting-the-id-of-a-row-i-updated-in-sql-server) – elyor Oct 24 '18 at 16:37
  • @elyor i dont have a primary id for that row, the primary id is a composite – Gagandeep Singh Oct 24 '18 at 16:45
  • @GagandeepSingh, maybe, first you select one row for updating, and then do all your updating on that row subsequently. – elyor Oct 24 '18 at 16:59
  • 3
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Oct 24 '18 at 17:13
  • Take a look at the `OUTPUT` clause to capture the key of the updated row. – Eric Brandt Oct 24 '18 at 17:38
  • Possible duplicate of [SQL update top1 row query](https://stackoverflow.com/questions/3860975/sql-update-top1-row-query) – Richardissimo Oct 24 '18 at 20:56

1 Answers1

0

I am rather confused. Your code has 5 update statements. If you have a need for the most recently updated row, then add a column updatedAt with the date/time of the update.

You can keep this up-to-date using a trigger. Or just include getdate() in each update. A trigger is, of course, preferred because it is guaranteed to update the column.

Then, you can get the most recently updated row for any table.

That said, the most recent updated row might have nothing to do with sp_updates (side note: do not use the sp_ prefix for user-defined stored procedures). SQL is multi-threaded and someone else might have updated a row.

If you really need this information after the procedure has run, then do the following:

  • Add a unique identity column in each table
  • Add a separate output parameter for the id of each table you care about
  • Assign the updated id in the stored procedure

Voila! You'll have the information after you have called the procedure.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786