Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting the Id of a row I updated in Sql Server

Tags:

sql

sql-server

I'm trying to return the Id of a row I update in sql

UPDATE ITS2_UserNames   SET AupIp = @AupIp   WHERE @Customer_ID = TCID AND @Handle_ID = ID    SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, SCOPE_IDENTITY() AS ID 

and I keep getting Null for the ID, how can I get this?

like image 218
Bob The Janitor Avatar asked Oct 22 '09 23:10

Bob The Janitor


People also ask

How do I get the updated record ID in SQL?

The updated record is available in the INSERTED table. The following Trigger is fetching the CustomerId of the updated record. In order to find which column is updated, you will need to use UPDATE function and pass the Column name of the Table to it.

How do I find the row ID in SQL Server?

If employee_id is an IDENTITY column and you are sure that rows aren't manually inserted out of order, you should be able to use this variation of your query to select the data in sequence, newest first: SELECT ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID, EMPLOYEE_ID, EMPLOYEE_NAME FROM dbo.

How do I find the last updated record ID in SQL Server?

To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.

How can I get identity value after inserting SQL Server?

Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session.


1 Answers

The @@identity and scope_identity() will hand you the identity of a new row, ie. after an insert. After your update, the identity of the row is... @Customer_ID or @Handle_Id? If it is a different field, you should use the OUTPUT clause to return the ID of the updated row:

UPDATE ITS2_UserNames   SET AupIp = @AupIp   OUTPUT INSERTED.PrimaryKeyID WHERE @Customer_ID = TCID AND @Handle_ID = ID 
like image 89
Remus Rusanu Avatar answered Sep 18 '22 12:09

Remus Rusanu