· 10 years ago · Sep 09, 2016, 05:04 PM
1MyTable
2-----------
3RowID int not null identity(1,1) primary key,
4Col1 varchar(20) not null,
5Col2 varchar(2048) not null,
6Col3 tinyint not null
7
8DELETE MyTable
9FROM MyTable
10LEFT OUTER JOIN (
11 SELECT MIN(RowId) as RowId, Col1, Col2, Col3
12 FROM MyTable
13 GROUP BY Col1, Col2, Col3
14) as KeepRows ON
15 MyTable.RowId = KeepRows.RowId
16WHERE
17 KeepRows.RowId IS NULL
18
19MIN(RowId)
20
21CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))
22
23;
24
25--Ensure that any immediately preceding statement is terminated with a semicolon above
26WITH cte
27 AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3
28 ORDER BY ( SELECT 0)) RN
29 FROM #MyTable)
30DELETE FROM cte
31WHERE RN > 1;
32
33DELETE dupes
34FROM MyTable dupes,
35 MyTable fullTable
36WHERE dupes.dupField = fullTable.dupField
37AND dupes.secondDupField = fullTable.secondDupField
38AND dupes.uniqueField > fullTable.uniqueField
39
40DELETE FROM TableName
41WHERE ID NOT IN (SELECT MAX(ID)
42 FROM TableName
43 GROUP BY Column1,
44 Column2,
45 Column3
46 /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
47 nullable. Because of semantics of NOT IN (NULL) including the clause
48 below can simplify the plan*/
49 HAVING MAX(ID) IS NOT NULL)
50
51SELECT YourColumnName,
52 COUNT(*) TotalCount
53FROM YourTableName
54GROUP BY YourColumnName
55HAVING COUNT(*) > 1
56ORDER BY COUNT(*) DESC
57
58delete t1
59from table t1, table t2
60where t1.columnA = t2.columnA
61and t1.rowid>t2.rowid
62
63delete
64from table t1
65using table t2
66where t1.columnA = t2.columnA
67and t1.rowid > t2.rowid
68
69DELETE LU
70FROM (SELECT *,
71 Row_number()
72 OVER (
73 partition BY col1, col1, col3
74 ORDER BY rowid DESC) [Row]
75 FROM mytable) LU
76WHERE [row] > 1
77
78DELETE FROM Mytable
79WHERE RowID NOT IN (SELECT MIN(RowID)
80 FROM Mytable
81 GROUP BY Col1,Col2,Col3)
82
83--DELETE FROM table1
84--WHERE id IN (
85 SELECT MIN(id) FROM table1
86 GROUP BY col1, col2, col3
87 -- could add a WHERE clause here to further filter
88 HAVING count(*) > 1
89--)
90
91select distinct * into t2 from t1;
92delete from t1;
93insert into t1 select * from t2;
94drop table t2;
95
96SELECT DISTINCT *
97 INTO tempdb.dbo.tmpTable
98FROM myTable
99
100TRUNCATE TABLE myTable
101INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
102DROP TABLE tempdb.dbo.tmpTable
103
104EMPLOYEE_ID ATTENDANCE_DATE
105A001 2011-01-01
106A001 2011-01-01
107A002 2011-01-01
108A002 2011-01-01
109A002 2011-01-01
110A003 2011-01-01
111
112ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)
113
114DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
115 FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)
116
117WITH CTE AS
118(
119SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
120FROM MyTable
121)
122
123DELETE FROM CTE WHERE RN<>1
124
125WITH CTE AS
126(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
127FROM MyTable)
128Â
129DELETE CTE
130WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
131
132begin transaction
133-- create temp table with identical structure as source table
134Select * Into #temp From tableName Where 1 = 2
135
136-- insert distinct values into temp
137insert into #temp
138select distinct *
139from tableName
140
141-- delete from source
142delete from tableName
143
144-- insert into source from temp
145insert into tableName
146select *
147from #temp
148
149rollback transaction
150-- if this works, change rollback to commit and execute again to keep you changes!!
151
152;with cte as (
153 select
154 min(PrimaryKey) as PrimaryKey
155 UniqueColumn1,
156 UniqueColumn2
157 from dbo.DuplicatesTable
158 group by
159 UniqueColumn1, UniqueColumn1
160 having count(*) > 1
161)
162delete d
163from dbo.DuplicatesTable d
164inner join cte on
165 d.PrimaryKey > cte.PrimaryKey and
166 d.UniqueColumn1 = cte.UniqueColumn1 and
167 d.UniqueColumn2 = cte.UniqueColumn2;
168
169DELETE
170 tbl
171FROM
172 MyTable tbl
173WHERE
174 EXISTS(SELECT * FROM MyTable tbl2 WHERE tbl2.SameValue = tbl.SameValue AND tbl.IdUniqueValue < tbl2.IdUniqueValue)
175
176DELETE FROM myTable WHERE RowID IN (
177 SELECT MIN(RowID) AS IDNo FROM myTable
178 GROUP BY Col1, Col2, Col3
179 HAVING COUNT(*) = 2 )
180
181DELETE FROM MyTable WHERE NOT RowID IN
182 (SELECT
183 (SELECT TOP 1 RowID FROM MyTable mt2
184 WHERE mt2.Col1 = mt.Col1
185 AND mt2.Col2 = mt.Col2
186 AND mt2.Col3 = mt.Col3)
187 FROM MyTable mt)
188
189WITH tblTemp as
190(
191SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
192 As RowNumber,* FROM <table_name>
193)
194DELETE FROM tblTemp where RowNumber >1
195
196DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno)
197AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
198or empname not in
199(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno)
200AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
201
202SET ROWCOUNT 1 -- or set to number of rows to be deleted
203delete from myTable where RowId = DuplicatedID
204SET ROWCOUNT 0
205
206delete from table_name T1
207where rowid>(select min(rowid)
208from table_name T2 where T1.column_name=T2.column_name) ;
209
210-- given a table stories(story_id int not null primary key, story varchar(max) not null)
211CREATE TRIGGER prevent_plagiarism
212ON stories
213after INSERT, UPDATE
214AS
215 DECLARE @cnt AS INT
216
217 SELECT @cnt = Count(*)
218 FROM stories
219 INNER JOIN inserted
220 ON ( stories.story = inserted.story
221 AND stories.story_id != inserted.story_id )
222
223 IF @cnt > 0
224 BEGIN
225 RAISERROR('plagiarism detected',16,1)
226
227 ROLLBACK TRANSACTION
228 END
229
230DELETE
231FROM MyTable
232WHERE NOT EXISTS (
233 SELECT min(RowID)
234 FROM Mytable
235 WHERE (SELECT RowID
236 FROM Mytable
237 GROUP BY Col1, Col2, Col3
238 ))
239 );
240
241INSERT INTO tc_category1
242SELECT *
243FROM tc_category
244GROUP BY category_id, application_id
245HAVING count(*) > 1
246
247INSERT INTO tc_category1
248SELECT *
249FROM tc_category
250GROUP BY category_id, application_id
251HAVING count(*) = 1
252
253CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)
254
255INSERT INTO car(PersonId,CarId)
256VALUES(1,2),(1,3),(1,2),(2,4)
257
258--SELECT * FROM car
259
260;WITH CTE as(
261SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)
262
263DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)
264
265with MYCTE as (
266 SELECT ROW_NUMBER() OVER (
267 PARTITION BY DuplicateKey1
268 ,DuplicateKey2 -- optional
269 ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed
270 ) RN
271 FROM MyTable
272)
273DELETE FROM MYCTE
274WHERE RN > 1
275
276DELETE A
277FROM TABLE A,
278 TABLE B
279WHERE A.COL1 = B.COL1
280 AND A.COL2 = B.COL2
281 AND A.UNIQUEFIELD > B.UNIQUEFIELD
282
283alter table MyTable add sno int identity(1,1)
284 delete from MyTable where sno in
285 (
286 select sno from (
287 select *,
288 RANK() OVER ( PARTITION BY RowID,Col3 ORDER BY sno DESC )rank
289 From MyTable
290 )T
291 where rank>1
292 )
293
294 alter table MyTable
295 drop column sno