Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    8

    Unanswered: 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?

    eWW

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    The procedure accepts a recipientID to check - did you want it to process all of the recipients in the table at once?

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    5

    Lightbulb Re: copy the data from one field of one row to the same field of another row

    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

  5. #5
    Join Date
    Jan 2003
    Posts
    8
    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
    Last edited by eWW; 01-29-03 at 05:10.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •