How does one Delete multiple rows of the same data in the same table but retain one? Delete a table with multiple rows of the same data, but keep one row of data, as shown in the following table:
ID name cdefine1 cdefine2 cdefine3
1 aa 101 09-08
2 AA 101 09-09
3 BB 101 09-09
4 cc 204 09-12
5 cc 204 09-13
6 dd 255 09-05
7 EE 255 09-06
8 EE 211
Now I want to delete the data with the same name and cdefine1 fields in the table. That is to say, if the name and cdefine1 fields are the same, delete them, but keep one. The final result is as follows:
ID name cdefine1 cdefine2 cdefine3
1 aa 101 09-08
2 BB 101 09-09
3 cc 204 09-12
4 dd 255 09-05
5 ee 255 09-06
8 EE 211
------ Solution --------------------------------------------------------
SQL code
Delete tfrom TB twhere exists (select 1 from TB where name = T. Name and cdefine1 = T. cdefine1 and ID> T. ID) ------ solution --------------------------------------------------------
SQL code
delete tfrom tb twhere exists (select 1 from tb where name=t.name and cdefine1=t.cdefine1 and id>t.id)