sql select biggest updated primary key

Question

Is there any way to get biggest primary key value of the table after update statement?

I have a C# function takes parameters like, table name, and values to update a table. It generates a dynamic sql statement and executes it. So when I run function, if there is an insert it returns me last inserted id as int, there is not problem but if there is an update then I cannot get biggest updated id of the table.

for example I have a table like; RowId, Name, Lastname

-- this statement may update multiple rows
Update Person
set name = 'sample name'
where name = 'name' and lastname = 'lastname'
-- get last updated rowid here

Last? In what order? Without an order, question makes no sense. — Mitch Wheat, Dec 11 '13 at 00:10
@SamD Yes I do, But I am tring to find a query which will work for all update statements, not individual select. — Teoman shipahi, Dec 11 '13 at 00:11
Are you sure about that? I PK 1,000,000 was updated last week and PK 3 was updated today, you want PK 1,000,000? — Dan Bracuk, Dec 11 '13 at 00:25
There is a solution for this in this thread. http://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by — Teoman shipahi, Dec 11 '13 at 14:09

score 2 · Accepted Answer · answered Dec 11 '13 at 00:19

Not 100% sure why you're after this, can you explain further. However, i'd use the OUTPUT syntax. For example:

DECLARE @UpdatedIDs TABLE
(
    ID INT
)

-- this statement may update multiple rows
Update Person
set name = 'sample name'
OUTPUT INSERTED.ID INTO @UpdatedIDs
where name = 'name' and lastname = 'lastname'
-- get last updated rowid here 

SELECT MAX(ID) AS LastUpdatedID
FROM @UpdatedIDs

You will end up with a variable table called @UpdatedIDs which you then select the MAX() ID from the rows you've just updated.

score 1 · Answer 2 · answered Dec 11 '13 at 00:17

1

You could use the OUTPUT clause to output all the rows that were updated into a table (variable).

Then you could manipulate that however you want.

But I fail to see the usefulness of knowing the 'last' or even the largest primary key updated. Perhaps you could explain why you need this?

Also, your question implies that you are using a Natural key for the table's Primary key. I would use a surrogate key instead. Use an integer IDENTITY column as the primary key.

answered Dec 11 '13 at 00:17

Mitch Wheat

295,962
43
465
541

I have a C# function takes parameters like, table name, and values to update a table. It generates a dynamic sql statement and executes it. So when I run function, if there is an insert it returns me last inserted id as int there is not problem but if there is an update then I cannot get last updated id of the table. – Teoman shipahi Dec 11 '13 at 00:26

score -1 · Answer 3 · answered Dec 11 '13 at 00:16

-1

Try with

UPDATE Person
SET name = 'sample name', @lastUpdatedId = id
WHERE name = 'name' and lastname = 'lastname'

answered Dec 11 '13 at 00:16

Nahuel Prieto

371
3
15

1

I'm not even sure if this syntax is documented, but I'm not sure how you can guarantee that it will give the "biggest" value of `id` that was updated, in the case where multiple rows are affected. [Here is one case where it breaks](http://sqlfiddle.com/#!3/a8dcd/1). – Aaron Bertrand Dec 11 '13 at 01:35
@AaronBertrand - There's an obscure corner of the Internet where the syntax is documented: [here](http://technet.microsoft.com/en-us/library/ms177523.aspx). Read that again: _"where the syntax is documented"_. Semantics are generally up for grabs. You may now post a link to your blog that undocuments the syntax. – HABO Dec 11 '13 at 03:24

sql select biggest updated primary key

3 Answers3