Is there a big to be solved with the train of thought-CodePudding

Topic 1.1, delete the duplicate records of table
If the same record is duplicate records, please write the SQL statements such conditions;
If you have id primary key (Numbers, since the increase 1 mode), please write the SQL statements such conditions,

CodePudding user response:

; With cte as (
The Select row_number () over (order by id desc) rids, *
The From TB
)
The Delete from cte where rids & gt; 1

CodePudding user response:

First get to heavy by whole group
The second word is through group id

CodePudding user response:

reference 1/f, gypsy song response:

; With cte as (
The Select row_number () over (order by id desc) rids, *
The From TB
)
The Delete from cte where rids & gt; 1

The delete from cte can realize physical delete directly?

If I have what the result is the join of cte?

CodePudding user response:

reference illiterate old gu reply: 3/f

Quote: refer to 1st floor gypsy song response:

; With cte as (
The Select row_number () over (order by id desc) rids, *
The From TB
)
The Delete from cte where rids & gt; 1

The delete from cte can realize physical delete directly?

If I have what the result is the join of cte?

 USE tempdb for 
GO 
IF OBJECT_ID (' ta ') IS NOT NULL 
DROP TABLE ta 
GO 
The CREATE TABLE ta (
Id INT IDENTITY (1, 1) PRIMARY KEY, 
N VARCHAR (10) 
) 
GO 
INSERT INTO ta (n) VALUES (' a ') 
INSERT INTO ta (n) VALUES (' b ') 
INSERT INTO ta (n) VALUES (' b ') 
GO 
Before deleting - 
SELECT * FROM ta 
/* 
Id n 
1 a 
2 b 
3 b 
*/
- delete 
; WITH cte AS (
The SELECT ROW_NUMBER () OVER (PARTITION BY n ORDER BY id DESC) AS rids, * FROM ta 
) 
The DELETE FROM cte WHERE rid> 1 

After deleting - 
SELECT * FROM ta 
/* 
Id n 
1 a 
3 b 
*/

As for multiple tables, you try yourself,
Personally don't recommend more table delete also so, even if can achieve, because of my poor readability, easy wrong operation,

CodePudding user response:

The first case used to same data analysis function row_number manmade number, then according to the number of deleted, reference upstairs
The first can solve, the second is the something