SQL statement Deletes the same data in the same table

The above is based on the id of your table, but it is true that if there is no id, it feels more difficult to delete it directly, but it can be done directly by a rogue method.

Name your table table

select distinct(name) into #name from tablename

delete from tablename

insert into tablename select * from #name;

Then select * from tablename to get the result.

What does it mean is obvious, establish a temporary table, delete all the data of the table, and then reverse the data of the temporary table, pass the test, and you can complete the requirements of the landlord.

It's a little hooligan.,But for this special situation on the special treatment of it.,It is recommended that the landlord still help this table to establish a unique column.,Then it's much easier to do other operations in the future.。

If you have only one column in this table, then there is no way, there must be a column with a unique constraint, such as the id field.

delete from table_namewhere id <>select max(id) id from table_name d

where table_

This makes it possible to keep only the one with the highest ID.

It can be the same as the name, ID

For example, delete from [table name] where id = 1

delete from table name where name ="Tiger Ping"

Add a serial number to the data so that it can be deleted.

1. First create a table and insert duplicate records into the table as shown in the following figure.

2. After inserting, you will see that there are already duplicate data in the table, as shown in the figure below.

3. Next, we remember to back up before deleting, as shown in the figure below.

4, Then the exclusion of duplicate records can be set by the distinct field as shown in the image below, and then the deduplicated data is inserted into the new table.

5. Then you can see that a new table has just been created under the data table, as shown in the following figure.

6. Finally, open the new table, you can see that the duplicate data is gone, as shown in the figure below.

There are two ways, one is nested and the other is associated.

Nested: Table 1 contains the following data.

id name

1 card three. 2 Li Si.

3 Kings V. Table 2 contains the following data.

idTo delete the data in Table 1 that contains the id in Table 2, you can use the following statement:

delete from Table 1 where id in (select id from Table 2).

Associations: Table 1 lists the following data.

id name

1 card three. 2 Li Si.

3 Kings V. Table 2 contains the following data.

id name

1 card three. 2 Haha.

To delete the data in Table 1 where both ID and Name are equal to ID and Name in Table 2, you can use the following statement:

delete from Table1 where exists (select 1 from Table2 where table and table;

1. You can use the following statements to deal with it, and you can do it according to the annotations:

Deduplicate the data and stage it in temporary table A.

select distinct * into a from table1 where condition.

delete table1 where --Note: Insert into table1 select * from a -- Insert into table1 select * from a -- Insert the staged data back into the database.

drop table a -- Delete the temporary table.

2. In addition, in the current database, it is recommended that each table should have a flag field to ensure that the records are not completely duplicated, otherwise it is very easy to have problems in practice.

Add an auto-incrementing column to the table, then get the larger column through the statement and delete it.

There are two ways, one is nested and the other is associated.

Nested: Table 1 contains the following data.

id name

1 card three. 2 Li Si.

3 Kings V. Table 2 contains the following data ID1

2To delete the data in Table 1 that contains the IDs in Table 2, you can use the following statement:

delete from Table 1 where id in (select id from Table 2).

Associations: Table 1 lists the following data.

id name

1 card three. 2 Li Si.

3 Kings V. Table 2 contains the following data.

id name

1 card three. 2 Haha.

To delete the data in Table 1 where both ID and Name are equal to ID and Name in Table 2, you can use the following statement:

delete from Table1 where exists (select 1 from Table2 where table and table;