July 7, 2010 at 9:16 am
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
July 7, 2010 at 10:32 am
Here you go
select ID,Model,
(select (count(client) +1 )* -1 as cnt
from table1 i where i.Model = o.Model and i.ID < o.ID ) 'client'
from table1 o
July 7, 2010 at 10:32 am
You could do a quirky update. Something like this, although I had to add a clustered index on ModelID and ClientID desc to get the update to work. I put it in a transaction and rolled it back for testing
create table #table1
(
ID int,
ModelID int ,
ClientID int
)
CREATE CLUSTERED INDEX IX_1 ON #table1
(
ModelID, ClientID desc
)
GO
insert into #table1
select 1,1,-1 union all
select 2,1,-2 union all
select 3,1,-2 union all
select 4,1,-3 union all
select 5,1,-3 union all
select 6,2,-1 union all
select 7,2,-2 union all
select 8,2,-3 union all
select 9, 1, -10 union all
select 11,1,-5 union all
select 12,2,-3 union all
select 13,1,-6
declare @ClientID int, @ModelID int
begin tran
update #table1
set @ClientID = ClientID =
case when @ModelID = ModelID THEN
Case when ClientID = @ClientID then @ClientID -1
when @ClientID < ClientID then @ClientID - 1
else ClientID end
ELSE ClientID
end,
@ModelID = ModelID
OPTION (MAXDOP 1)
select * from #table1
rollback tran
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 10:33 am
I'll bet you didn't know you can update variables at the same time as columns in an UPDATE statement. This should work for you.
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 ...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 7, 2010 at 10:34 am
Or use the windowed functions:
To get your exact result:
-- *** Test Data ***
DECLARE @t TABLE
(
ID int NOT NULL
,ModelID int NOT NULL
,ClientId int NOT NULL
)
INSERT INTO @t
SELECT 1, 1, -1
UNION ALL SELECT 2, 1, -2
UNION ALL SELECT 3, 1, -2
UNION ALL SELECT 4, 1, -3
UNION ALL SELECT 5, 1, -3
UNION ALL SELECT 6, 2, -1
UNION ALL SELECT 7, 2, -2
UNION ALL SELECT 8, 2, -3
-- *** End Test Data ***
;WITH T1
AS
(
SELECT ID, ModelID, ClientId
,ROW_NUMBER() OVER (PARTITION BY ModelID, ClientId ORDER BY ID) AS RowNum
,MIN(ClientId) OVER (PARTITION BY ModelID) AS MinClientID
FROM @t
)
, T2
AS
(
SELECT ID, ClientId, MinClientID, ROW_NUMBER() OVER (PARTITION BY ModelID ORDER BY ID) AS RowNum
FROM T1
WHERE RowNum > 1
)
UPDATE T2
SET ClientID = MinClientID - RowNum
-- Check Result
SELECT *
FROM @t
or just to re-number ClientID:
-- *** Test Data ***
DECLARE @t TABLE
(
ID int NOT NULL
,ModelID int NOT NULL
,ClientId int NOT NULL
)
INSERT INTO @t
SELECT 1, 1, -1
UNION ALL SELECT 2, 1, -2
UNION ALL SELECT 3, 1, -2
UNION ALL SELECT 4, 1, -3
UNION ALL SELECT 5, 1, -3
UNION ALL SELECT 6, 2, -1
UNION ALL SELECT 7, 2, -2
UNION ALL SELECT 8, 2, -3
-- *** End Test Data ***
;WITH T
AS
(
SELECT ID, ClientId
,ROW_NUMBER() OVER (PARTITION BY ModelID ORDER BY ID) AS RowNum
FROM @t
)
UPDATE T
SET ClientId = -RowNum
FROM T
WHERE ClientId <> -RowNum
-- Check Result
SELECT *
FROM @t
July 7, 2010 at 10:58 am
Thanks for the reply's.
Wayne's solution looks closest to what i am after, but i think it'll need a little tweaking. I'll keep you all updated with how I get along and then post a solution once I have tested.
Toby
July 12, 2010 at 3:23 am
Just updating the thread to show my solution
Not the most elegant solution, but it works....
-- For the sake of testing I have used table variables
DECLARE @Table1 TABLE ( ID INT PRIMARY KEY,
ModelID INT,
ClientID INT)
DECLARE @Table2 TABLE ( ID INT PRIMARY KEY,
ModelID INT,
ClientID INT)
INSERT @Table1 ( ID, ModelID, ClientID )
SELECT 1, 1, -1 UNION
SELECT 2, 1, -2 UNION
SELECT 3, 1, -2 UNION
SELECT 4, 1, -3 UNION
SELECT 5, 1, -3 UNION
SELECT 6, 2, -1 UNION
SELECT 7, 2, -2 UNION
SELECT 8, 2, -3
DECLARE @ID INT
-- Grab a list of duplicates and save them into the temporary table @Table2
INSERT INTO @Table2 ( ID, ModelID, ClientID )
SELECT
(SELECT MAX(ID) FROM @Table1 WHERE ModelID = t1.ModelID and ClientID = t1.ClientID) ID,
ModelID ,
ClientID
FROM @Table1 t1
GROUP BY ModelID, ClientID
HAVING COUNT(ClientID) > 1
-- Loop over all rows in @Table2 and update them one at a time
-- so that the next client ID is calculated properly
WHILE EXISTS (SELECT * FROM @Table2)
BEGIN
-- Get the next ID from @Table2
SELECT TOP 1 @ID = ID FROM @Table2 ORDER BY ID )
-- Update the ClientID to the latest available
UPDATE @Table1
SET ClientID = (SELECT MIN(ClientID)-1 FROM @Table1 WHERE ModelID = t1.ModelID)
FROM @Table1 T1
INNER JOIN @Table2 T2
ON T2.ID = T1.ID
WHERE T2.ID = @ID
-- Remove the row from the @Table2 table (so that the next loop iteration will pick up the next available row to fix)
DELETE FROM @Table2 WHERE ID = @ID
END
-- Ta-da!
SELECT
ModelID ,
ClientID
FROM @Table1 t1
GROUP BY ModelID, ClientID
HAVING COUNT(ClientID) > 1
Problem with Wayne's solution is that 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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply