Delete duplicate records in the Table. Only one row is retained.

Source: Internet
Author: User
  1. The SQL statement I used:
  2. Delete from fp_sgfpxx
    Where fphm in (select fphm from fp_sgfpxx group by fphm having count (fphm)> 1)
    And rowid not in (select min (rowid) from fp_sgfpxx group by fphm having count (fphm)> 1)
  3. Delete duplicate invoice numbers
  4. SQL statement used to query Repeated Records of multiple fields in the same table
  5. From: 7th string
  6. For example, there is a personnel table (table name: peosons)
  7. Select p1. * from persons p1, persons p2 where p1.id <> p2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address
  8. The above results can be achieved.
  9. Several SQL statements used to delete Repeated Records
  10. 1. Use the rowid Method
  11. 2. Use the group by Method
  12. 3. Use the distinct method
  13. 1. Rowid Method
  14. The statement is as follows:
  15. Query data:
  16. Select * from table1 a where rowid! = (Select max (rowid)
  17. From table1 B where a. name1 = B. name1 and a. name2 = B. name2 ......)
  18. Delete data:
  19. Delete from table1 a where rowid! = (Select max (rowid)
  20. From table1 B where a. name1 = B. name1 and a. name2 = B. name2 ......)
  21. 2. group by Method
  22. Query data:
  23. Select count (num), max (name) from student -- lists the number of repeated records and its name attribute
  24. Group by num
  25. Having count (num)> 1 -- group by num to find the num column in the table, that is, more than once
  26. Delete data:
  27. Delete from student
  28. Group by num
  29. Having count (num)> 1
  30. In this way, all duplicates are deleted.
  31. 3. Use the distinct method-useful for small tables
  32. Create table table_new as select distinct * from table1 minux
  33. Truncate table table1;
  34. Insert into table1 select * from table_new;
  35. How to query and delete duplicate records
  36. 1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).
  37. Select * from people
  38. Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
  39. 2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid
  40. Delete from people
  41. Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
  42. And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)
  43. 3. Search for redundant duplicate records in the table (multiple fields)
  44. Select * from vitae
  45. Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
  46. 4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
  47. Delete from vitae
  48. Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
  49. And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)
  50. 5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
  51. Select * from vitae
  52. Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
  53. And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)
  54. (2)
  55. For example
  56. There is A field "name" in Table ",
  57. The "name" value may be the same for different records,
  58. Now, you need to query items with duplicate "name" values between records in the table;
  59. Select Name, Count (*) From A Group By Name Having Count (*)> 1
  60. If the gender is also the same, the statement is as follows:
  61. Select Name, sex, Count (*) From A Group By Name, sex Having Count (*)> 1
  62. (3)
  63. Method 1
  64. Declare @ max integer, @ id integer
  65. Declare cur_rows cursor localForSelect Main field, count (*) from table name group by main field having count (*)>; 1
  66. Open cur_rows
  67. Fetch cur_rows into @ id, @ max
  68. While@ Fetch_status = 0
  69. Begin
  70. Select @ max = @ max-1
  71. Set rowcount @ max
  72. Delete from table name where primary field = @ id
  73. Fetch cur_rows into @ id, @ max
  74. End
  75. Close cur_rows
  76. Set rowcount 0
  77. Method 2
  78. "Repeat record" has two duplicate records. One is a completely repeated record, that is, a record with all fields already exists. The other is a record with duplicate key fields, for example, the Name field is repeated, while other fields are not necessarily repeated or can be ignored.
  79. 1. For the first type of repetition, it is easier to solve.
  80. Select distinct * from tableName
  81. You can get the result set without repeated records.
  82. If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:
  83. Select distinct * into # Tmp from tableName
  84. Drop table tableName
  85. Select * into tableName from # Tmp
  86. Drop table # Tmp
  87. The reason for this repetition is that the table design is not weekly. You can add a unique index column.
  88. 2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
  89. Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.
  90. Select identity (Int, 1, 1) as autoID, * into # Tmp from tableName
  91. Select min (autoID) as autoID into # Tmp2 from # Tmp group by Name, autoID
  92. Select * from # Tmp where autoID in (select autoID from # tmp2)
  93. The last select command gets the result set with no duplicate Name and Address (but an autoID field is added, which can be omitted in the select clause when writing)
  94. (4)
  95. Duplicate Query
  96. Select * from tablename where id in (
  97. Select id from tablename
  98. Group by id
  99. Having count (id)> 1
  100. )
  101. ======================================
  102. There are several identical records in thousands of records. How can I use SQL statements to delete duplicates? Thank you!
    1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).
    Select * from people
    Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

    2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid
    Delete from people
    Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
    And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

    3. Search for redundant duplicate records in the table (multiple fields)
    Select * from vitae
    Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

    4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
    Delete from vitae
    Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
    And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

    5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
    Select * from vitae
    Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
    And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

    For example, there is A field "name" in Table A, and the "name" values may be the same between different records,
    Now, you need to query items with duplicate "name" values between records in the table;
    Select Name, Count (*) From A Group By Name Having Count (*)> 1

    If the gender is also the same, the statement is as follows:
    Select Name, sex, Count (*) From A Group By Name, sex Having Count (*)> 1

    2. group by Method

    Query data:
    Select count (num), max (name) from student -- lists the number of repeated records and its name attribute
    Group by num
    Having count (num)> 1 -- group by num to find the num column in the table, that is, more than once
    Delete data:
    Delete from student
    Group by num
    Having count (num)> 1
    In this way, all duplicates are deleted.

    3. Use the distinct method-useful for small tables

    Create table table_new as select distinct * from table1 minux
    Truncate table table1;
    Insert into table1 select * from table_new;

    Delete select a. * from FLRK1 a inner join FLRK1 B on a. Record Number = B. Record Number and
    (A. [ID] = B. [ID] and a. warehouse receiving date = B. warehouse receiving date and a. operation time = B. Operation Time)

    Delete from FLRK1 where record number in
    (Select min (Record Number) from FLRK1 group by record number having count (Record Number)> 1)

    Table A structure:

    ID RQ SJ C
    --------------------------------------------
    1 2005-07-14 14:20:50 A1
    2 05:12:23 A1
    3 2005-07-14 14:20:50 A1
    4 2005-06-16 16:16:16 A2
    5 16:16:16 A2
    6 05:10:35 A3
    7 05:12:23 A1

    --------------------------------------------

    Find one SQL statement and delete the same record for the RQ, SJ, and C fields in table.

    Result:

    ID RQ SJ C
    --------------------------------------------
    1 2005-07-14 14:20:50 A1
    2 2005-02-15 05:12:23 A1
    4 2005-06-16 16:16:16 A2
    6 05:10:35 A3
    --------------------------------------------

    Delete from A Where ID Not In (Select Min (ID) from A Group By RQ, SJ, C)

    Delete a from tb a inner join tb as B on. fid <B. fid and. c = B. c and. rq = B. rq and. sj = B. sj

    Delete from A t
    Where exists (select 1 from A where ID <A. ID and SJ = t. SJ and RQ = t. RQ and C = t. c)

    Method 1

    Declare @ max integer, @ id integer
    Declare cur_rows cursor local for select Main field, count (*) from table name group by main field having count (*)> 1
    Open cur_rows
    Fetch cur_rows into @ id, @ max
    While fetch_status = 0
    Begin
    Select @ max = @ max-1
    Set rowcount @ max
    Delete from table name where primary field = @ id
    Fetch cur_rows into @ id, @ max
    End
    Close cur_rows
    Set rowcount 0

    Method 2

    There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate Name fields, other fields are not necessarily repeated or can be ignored.

    1. For the first type of repetition, it is easier to solve.

    Select distinct * from tableName

    You can get the result set without repeated records.
    If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

    Select distinct * into # Tmp from tableName
    Drop table tableName
    Select * into tableName from # Tmp
    Drop table # Tmp

    The reason for this repetition is that the table design is not weekly. You can add a unique index column.

    2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

    Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.

    Select identity (int, 1, 1) as autoID, * into # Tmp from tableName
    Select min (autoID) as autoID into # Tmp2 from # Tmp group by Name, autoID
    Select * from # Tmp where autoID in (select autoID from # tmp2)

    The last select command gets the result set with no duplicate Name and Address (but an autoID field is added, which can be omitted in the select clause when writing)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.