copy the data from one field of one row to the same field of another row

I have been puzzling over this for nearly two days now and I'm about ready to dispair.

The task seems simple:

There are a few rows in a table that looks like this:

RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...

After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this

info@.test.com,Tester, 1, 07/18/2002

The closest I got to a solution was this:

CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),

AS

UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)

GO

This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.

Can anyone see how to copy selectable data from one row to another?

eWWThe procedure accepts a recipientID to check - did you want it to process all of the recipients in the table at once?|||Not too clear what you want but something like this maybe

update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1

If there can only be one rec with FreshUpload - 0 then

update tbl
set Removed = t2.Removed
from tbl, tbl t2
where t2.RecipientID = tbl.RecipientID
and t2.Name = tbl.Name and t2.FreshUpload = 0
and tbl.FreshUpLoad = 1|||Hi,

We can use a cursor and instead of updating the "Removed" field, y cant we jus update the "FreshLoad" field with a 1 when the match is found.
Does this query help??
************************************************** ******
declare updaterec cursor
for
select * from <Tablename> where FreshUpload=1

open updaterec

fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed

while @.@.fetch_status=0
begin
Update <Tablename> set FreshLoad=1 where RecipientID=@.varRecipientID
fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed
end

close updaterec
deallocate updaterec
************************************************** *****
Please tell me if this helps.
Regards,
Ramya

Originally posted by eWW
I have been puzzling over this for nearly two days now and I'm about ready to dispair.

The task seems simple:

There are a few rows in a table that looks like this:

RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...

After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this

info@.test.com,Tester, 1, 07/18/2002

The closest I got to a solution was this:

CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),

AS

UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)

GO

This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.

Can anyone see how to copy selectable data from one row to another?

eWW|||Nigelrivett: I tried your solution first as it looked the leanest and BINGO!
That's it. You solved my riddle. Amazing how simple this can be when you know what you're doing.

Thank you very much nigelrivett!

and thank all of you who have helped.

eWW

[QUOTE][SIZE=1]Originally posted by nigelrivett
Not too clear what you want but something like this maybe

update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1