Help needed with an MS SQL query

[redeye_uk]

I have some database corruption and need to write an SQL script to rectify it.

Basically I have something similar to the following

table1
{
 ID int PK
 ModelID int FK
 ClientID int
}

and some typical data might look like

ID   ModelID    ClientID
1        1             -1
2        1             -2
3        1             -2
4        1             -3
5        1             -3
6        2             -1
7        2             -2
8        2             -3

so each entry for a model should have a unique ClientID. But you will note that model 1 has duplicate rows for clientID -2 & -3

Now I cant delete these duplicate rows, instead I need to update the ClientID value. So for ID 3 I need to update the ClientID to -4 and for ID 5 I need to update the ClientID to -5. (basically the next available ID)

Hopefully you're still following me I have written a query to get the duplicate rows from the table and tell me what the current highest ClientID used for that Model is.

select 
t1.ClientID, 
t1.ModelID, 
(SELECT TOP(1) KnowdeID from Table1 where ModelID = t1.ModelID and ClientID = t1.ClientID) ID, 
(SELECT min(ClientID)  from Table1 where ModelID = t1.ModelID) HighestClientID
from Table1 t1
group by t1.ClientID, t1.ModelID
having count(t1.ClientID) > 1

So this query will give me something like

ClientID   ModelID   ID    HighestClientID
   -2        1       3           -3
   -3        1       5           -3

How can I tweak the above query so I do an update and change the ClientID to "HighestClientID" + 1. In the above result set it would work for the first row, but not the second. i.e. it would still leave me with duplicates for ClientID -3

 

[redeye_uk]

Cheers Pho, exactly what I was after. I agree it's wonderfully over-engineered but its a change script so performance isn't too much of an issue

I was wanting to be really elegant and do the whole thing in a single query by updating variables at the same time as columns in an UPDATE statement. so something like....

declare @clientid int
select @clientid = select max(clientid)+1 from Table1 -- whatever you have to do to get the next number
update t1
   set clientid = @clientid,
       @clientid = @clientid + 1
  from Table1 t1
 where ...

.. problem with that is it'll only work for a single Model with duplicates (in reality I have multiple models with duplicates). I'm sure some t-sql demigod can prove me wrong, but time is against me so the while loop will have to suffice.