Need to UPDATE with PARAMETER / PARTITION / RANK. Please help.

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.

Assuming ID is the primary key, you can just do something like this:

update dbo.acmdtn
set Active = 1
where id in (
SELECT id
FROM (SELECT dbo.acmdtn.*,
row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1) )
and active <> 1