November 22, 2012 at 2:14 pm
I have made some mistakes in my programming, ending op in a 1:many, where one of the 'Many' is dulicated.
I want to make a script, that corrects this - Have been trying a coupple of days, but not finding out anything.
I have the table dutyrostershift, whis has a field ID (INT)
I have a table TimeAccountMovement, which has several fields, but two interesting here.
Ownerid (int)
TimeAccountID (int)
the bind between the two tables is timeaccountmovement.ownerid = dutyrostershift.id
there can be many timeaccountmovements corrssponding to the dutyrostershift, but there should be only one of each Timeaccountmovement.TimeAccountID
Problem is, that i have made several here and there.:blush:
They are always identical meaning that it don't make any difference which is deleted.
But 'how to' is the big question
What should I look at?
Best regards
Edvard Korsbæk
November 22, 2012 at 3:44 pm
I'm sorry, but I got lost in the explanation. Could you read the article linked in my signature and come back to post DDL, sample data and expected results? (it's all explained in the article).
November 22, 2012 at 4:11 pm
Like Luis Cazares - has said
But here is some T-SQL that you should be able follow and experiment with (USING tempdb of course) and with modifications make it the tool you need to solve your particular problem.
CREATE TABLE #T(Date_Stamp DATETIME,KW1 DECIMAL(5,1), KW2 DECIMAL(5,1))
INSERT INTO #T
SELECT '12/10/2010', 5.3, 3.1 UNION ALL
SELECT '12/10/2010', 5.3, 3.1 UNION ALL
SELECT '12/9/2010', 4, 2 UNION ALL
SELECT '12/8/2010', 3, 1 UNION ALL
SELECT '12/7/2010', 7.4, 5 UNION ALL
SELECT '12/7/2010', 7.4, 5 UNION ALL
SELECT '12/7/2010', 7.4, 5
;with cte
as (select row_number() over(partition by Date_Stamp,KW1,KW2 order by Date_Stamp) as rn,
KW1,KW2
from #T)
-- to show complete results:
SELECT * FROM cte
/*results:
rnKW1KW2
17.45.0
27.45.0
37.45.0
13.01.0
14.02.0
15.33.1
25.33.1
-- to show only duplicates
SELECT * FROM cte where ron_number > 1
November 22, 2012 at 4:18 pm
Because you don't care which are deleted, you can use a CTE with a row number:
Something like this:
;WITH dupefinder AS
(
SELECT
OwnerID,
TimeAccountID,
ROW_NUMBER() OVER(PARTITION BY OwnerID, TimeAccountID ORDER BY @@SPID) as RowNumber
FROM Timeaccountmovement
)
DELETE
FROM dupefinder
WHERE RowNumber>1
This assigns numbers to each row in the table, restarting at one(1) for every combination of OwnerID and TimeAccountID.
So, if you have a duplicate, you will get RowNumbers 1 and 2. For a triplicate, you get 1,2,3.
Then it is simple to delete anything that is not RowNumber 1.
The "ORDER BY @@SPID" clause is just there because you have to ORDER BY something - in this case you stated that you
don't care about the order, so I used @@SPID which is constant , you could also use ORDER BY (SELECT NULL) if you prefer.
{edit: I was slow typing this, and a similar answer has already been posted, but seeing as I typed it, I figured I may as well post it}
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
November 22, 2012 at 11:52 pm
Everytime i execute deletion i always delete my rowid(bigint) this is the Primary Key Constraint: Rows don't duplicate.
Set rules for creating table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply