Menu
I have a table:
| foo | bar |
+-----+-----+
| a | abc |
| b | def |
| c | ghi |
| d | jkl |
| a | mno |
| e | pqr |
| c | stu |
| f | vwx |
I want to delete all rows containing duplicates by foo column so that the table should look like this:
| foo | bar |
+-----+-----+
| b | def |
| d | jkl |
| e | pqr |
| f | vwx |
What is the most efficient way to do this?
773
Remove duplicate values Select the range of cells that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates. Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.
TRUNCATE is faster than DELETE , as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE . Unlike DELETE , TRUNCATE does not return the number of rows deleted from the table.
To remove duplicate values, click Data > Data Tools > Remove Duplicates. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.
You can join a table from a subquery which returns only unique foo using LEFT JOIN. The rows that did not have a match on the subquery will be deleted as you desired, example
DELETE a
FROM TableName a
LEFT JOIN
(
SELECT foo
FROM TableName
GROUP BY Foo
HAVING COUNT(*) = 1
) b ON a.Foo = b.Foo
WHERE b.Foo IS NULL
For faster performance, add an index on column Foo.
ALTER TABLE tableName ADD INDEX(foo)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
