Get the highest updated multiple row update id

Question

Update: I am using Sql Server 2008 R2.

I am going to update a large number of lines and avoid unnecessary blocking, I will do this in matches about a thousand lines per update.

Using SET ROWCOUND I can limit the update to 1000 lines and with WHERE ID > x I can set which package it should run.

But for this I need to know the highest identifier of the batch just processed.

I could have the OUTPUT user return the entire infected identifier and find the highest code, but I would like to return only the highest identifier.

I tried this

 SELECT MAX(id) FROM ( UPDATE mytable SET maxvalue = (SELECT MAX(salesvalue) FROM sales WHERE cid = t.id GROUP BY cid) OUTPUT inserted.id FROM mytable t WHERE au.userid > 0 ) updates(id) 

But it gives me this error An A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.

BUT, if I try to insert the result into the table directly, it really

 CREATE TABLE #temp(id int) INSERT INTO #temp SELECT MAX(id) FROM ( UPDATE mytable SET maxvalue = (SELECT MAX(salesvalue) FROM sales WHERE cid = t.id GROUP BY cid) OUTPUT inserted.id FROM mytable t WHERE au.userid > 0 ) updates(id) drop table #temp 

Is there any workaround for this and can anyone explain why I can insert the result into the table, but not just return the result?

Answer 1

DO NOT USE ROWCOUNT for this (or at all) as BOL says:

Using SET ROWCOUNT will not affect DELETE, INSERT and UPDATE in the next version of SQL Server!

Do not use SET ROWCOUNT with DELETE, INSERT and UPDATE statements in the new development to work and plan to change applications that currently use it. In addition, for DELETE, INSERT, and UPDATE, which currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

You can do this with a table variable:

 DECLARE @Log TABLE (id INT NOT NULL); UPDATE TOP 1000 mytable SET maxvalue = (SELECT MAX(salesvalue) FROM sales WHERE cid = t.id GROUP BY cid) OUTPUT inserted.id INTO @Log FROM mytable t WHERE au.userid > 0 SELECT maxid = MAX(id) FROM @Log