update table using cursor?

问题:

Update

the purpose of this excerise is to eliminate passing the @RegModifiedDateTime again what i want is i should be able to read ModifiedDateTime by passing Id

example: if i pass Id = 564 then i should be able to read

`schoold_Id and ModifiedDateTime`

end update

here is how my table looks like for SchoolRegistration:

school_id       id     active        modifydatetime
--------------------------------------------------
432        564       1               2008-12-14 13:15:38.750
342        564       1              2008-12-14 14:15:50.470
353        564       1              2008-12-14 14:19:46.703

end update

how do i loop to update my SchoolRegistration table? the id might have 1 or many rows in the SchoolRegistration but the thing is that RegModifiedDateTime is a unique for concurrency purpose and i should loop to get the right modifydatetime for that id.

alter procedure [dbo].[del_schoolRegistration]
    @Id bigint, 
    @RegModifiedDateTime datetime
as
begin  
    declare @rowsAffected int
    begin tran 


        --registration
        update SchoolRegistration
                   set Active = 0,
                    ModifiedDateTime = getdate()            
        where (Id = @Id and RegModifiedDateTime = @RegModifiedDateTime or @RegModifiedDateTime is null )


    if (@rowsAffected < 1) begin
        rollback tran
    end
    else begin
        commit tran
    end

    return @rowsAffected

end 

回答1:

   --registration
    ;with tmp as (
        select *, rn=ROW_NUMBER() over (partition by ID order by RegModifiedDateTime desc)
        from SchoolRegistration
        where (Id = @Id and RegModifiedDateTime = @RegModifiedDateTime or @RegModifiedDateTime is null ))
    update tmp
               set Active = 0,
                ModifiedDateTime = getdate()            
    WHERE rn=1

What happens here is that if you did not know the RegModifiedDateTime you are looking for (by passing @RegModifiedDateTime as NULL), the query will catch them all for the ID due to @RegModifiedDateTime is null, but update ONLY the LATEST RegModifiedDateTime based on the row_numbering and CTE table definition.

EDIT

The above query retains the option to pass in a direct @RegModifiedDateTime should a record other than the latest need updating. To always update only the latest, drop the WHERE filter against @RegModifiedDateTime completely

   --registration
    ;with tmp as (
        select *, rn=ROW_NUMBER() over (partition by ID order by RegModifiedDateTime desc)
        from SchoolRegistration
        where Id = @Id)
    update tmp
               set Active = 0,
                ModifiedDateTime = getdate()            
    WHERE rn=1

回答2:

i endup using curor:

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO