How do I do an UPDATE in this complex query?

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, acmdtn_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. (acmdtn_RECID is the primary key)
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 run reports on 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 all of the the latest "id" records 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.

I would suggest changing this stored procedure to a function or perhaps making a version of this that is a function. Hang on and I'll try to show you. Maybe something like:

create function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_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
)

|||

I mocked this up with this table and data:

create table dbo.acmdtn
( id integer,
efctv_from_dt datetime,
efctv_to_dt datetime,
modify_ts datetime,
extractno integer,
Active integer,
acmdtn_RECID integer
)
go

insert into acmdtn
select 1, '1/1/7', '2/1/7', '1/1/7', 1, 0, 1 union all
select 1, '2/1/7', '4/1/7', '1/15/7', 2, 0, 1 union all
select 1, '4/1/7', '1/1/8', getdate(), 3, 0, 1 union all
select 2, '1/1/7', '3/1/7', '1/1/7', 1, 0, 2 union all
select 2, '3/1/7', '7/1/7', '2/15/7', 2, 0, 2
select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 0 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 0 2
*/

I tested the UPDATE like this:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_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
)

go

update selectCurrent_Acmdtn (1)
set Active = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 2
*/

Something to consider is the use of:

SELECT dbo.acmdtn.*,

This is dangerous because it is not intuitively obvious whether or not this statement will return all columns of the dbo.acmdtn table. This is because the meaning of this select statement is determined at function compile time and NOT at function execution time. Therefore, I strongly suggest that you alter this statement to explicitly list all columns returned by the select statement.

This problem comes into play whenever the structure of the table is altered because at that time the columns returned by this select are no longer the same as the columns contained in the table.

In addition, if the point of this function is only to perform the update then it would be better to eliminate from the select statement any columns that do not contribute to the update.

|||

Thanks alot for the reply. I'll give that a try.

(I think I screwed up a little because I posted this question multiple times. I kept getting a message that "the administrator may have deleted your post" and I couldn't find it on a search initially, so I kept on posting!)

The only thing that I didn't mention is that I have acmdtn_RECID as the primary key (It's an identy field that gets assigned when I do the import from the raw data, because the original raw data didn't have any primary keys).

Would that change your soloution any?

Thanks again.

|||

No, it will not really change this solution; however, you are correct in identifying that my test data would not be valid. It would be good to have an index based on extractno because this is what is used here for filtering the data.

Again, it would be good to eliminate the SELECT * syntax to pare down some of the data.

|||

Create a stored procedure to apply the UPDATE.

create PROCEDURE [dbo].[update_Current_acmdtn]

@.extractNum char(10)

AS

SET NOCOUNT ON;

with cte

as

(

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

)

update cte

set Active = 1

where rn = 1

return @.@.error

go

AMB

|||

Hunchback presents a good way of performing the update. I realized that I need to factor out a piece from my query so I amended my function similar to what Hunchback did. Also, I eliminated the SELECT * syntax:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( select id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID,
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

)

go

update selectCurrent_Acmdtn (1)
set Active = 1
where rn = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 2
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 11:10:56.793 3 0 3
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 4
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 5
*/

Thanks, Hunchback. :-)

|||

Thanks alot guys,

I got it to work.