SQL chunk update with JOIN

Question

I have a 2 tables in my DB, one contain data about clients (called Clients), the other table contains clientID, Guid, AddedTime and IsValid (called ClientsToUpdate).

ClientID is related to the clients table, Guid is a unique identifier, AddedTime is the time when the record was added to the table, and IsValid is a bit indicated if this ClientID was updated or not.

What I want to do, is update all the Clients that their ID is in ClientsToUpdate, the problem is, the ClientsToUpdate table contains over than 80,000 records and I am getting deadlocks.

What I though I can do, is update 2000 clients at a time, using a while loop or something similar.

MY Stored Procedure looks like:

UPDATE client SET LastLogin=GETDATE() 
FROM Clients client
JOIN ClientsToUpdate ctu ON client.ID = ctu.ClientID;

Any idea how can I do it?

Answer 1

declare @done table (ClientID int primary key)
while 1=1
    begin

    update  top (2000) c
    set     lastlogin = getdate()
    output  deleted.id into @done
    from    Clients c
    join    ClientsToUpdate ctu
    on      c.id = ctu.ClientID
    where   not exists
            (
            select  *
            from    @done d
            where   d.ClientID = ctu.ClientID
            )

    if @@rowcount = 0
        break
    end

Example at SQL Fiddle.