December 5, 2006 at 2:38 pm
Hi,
I have a following table, and rows with the same SectionID, RoomID, TimeID, DayID, PeriodID, and ScheduleTyID are delicately inserted. Column “SessionID” is a primary key of the table. So, on viewing the table, you see sectionID= 9634, 9636 have duplicate row insertions:
SectionID SessionID RoomID TimeID DayID PeriodID ScheduleTyID
----------- ----------- ----------- ----------- ----------- ----------- ------------ ------------------
9634 23444 21 135 6 30 2
9634 24312 21 135 6 30 2
9636 23401 9 71 8 15 3
9636 24269 9 71 8 15 3
9638 23180 9 108 13 29 4
I would like to remove row with sessionIDs of 23444 (bigger sessionIDs), 23401 of sectionIDs of 9634 and 9636. What is the best way (quick and less scripts) way to remove such duplicates?
Any help/hint is greatly appreciated.
JohnSql
December 5, 2006 at 2:51 pm
The quickest and best way to be sure you get it right in my opinion is this
Open Enterprise Manager and open the table (tablex since I don't have a name) then try the following
SELECT * FROM dbo.TableX X where X.SectionID IN (SELECT A.SectionID FROM dbo.TableX A GROUP BY A.SectionID HAVING COUNT(A.SectionID) > 1)
The you can highlight any row you don't want and delete it. I prefer this to be sure I can check each row and make a decision myself. I would also suggest if this is not something you want to have happen again at least place a Unique index or constraint on the column SectionID to prevet duplicates.
Don't overcomplicate the task itself.
December 5, 2006 at 3:00 pm
Delete from tbl
where SessionID NOT IN
(Select min(SessionID) from tbl group by SectionID, RoomID, TimeID, DayID, PeriodID, ScheduleTyID )
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply