How to Delete Duplicate Rows from a Database Table Without Creating a New Table?

Deleting Duplicate Rows from a Table

You seek a method to remove duplicate rows from a database table without creating a separate table. Consider the following table:

id	action	L11_data
L1_name	L1_data	L11_data1
L2_name	L2_data	L11_data2
L3_name	L3_data	L11_data3
L4_name	L4_data	L11_data4
L5_name	L5_data	L11_data1
L6_name	L6_data	L11_data2
L7_name	L7_data	L11_data5
L8_name	L8_data	L11_data1
L9_name	L9_data	L11_data6
L10_name	L10_data	L11_data2
L11_name	L11_data	L11_data7
L12_name	L12_data	L11_data8
L13_name	L13_data	L11_data1
L14_name	L14_data	L11_data6
L15_name	L15_data	L11_data9

Existing Duplicate Rows

As you mentioned, the table contains duplicate values in the L11_data column within each action group. Your goal is to retain only the unique L11_data values and their corresponding data for each action.

Solution

Assuming the table has a unique id field, you can execute the following query:

DELETE FROM Table
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Table
GROUP BY action, L11_data
)

Query Breakdown

• The outer DELETE statement deletes rows from the Table.
• The nested SELECT statement identifies the minimum ID for each unique combination of action and L11_data.
• The WHERE clause excludes rows with IDs not in the list of minimum IDs, thereby removing duplicate rows.

Notes

• The query will run slowly if the table has a large number of fields and rows.
• If the Table does not have a unique index, it is recommended to add one for performance reasons.

The above is the detailed content of How to Delete Duplicate Rows from a Database Table Without Creating a New Table?. For more information, please follow other related articles on the PHP Chinese website!