Delete Duplicate Rows in SQL

Have you ever found yourself staring at a large SQL delete statement wondering what it's doing? If so, you're not alone. In this article, we'll take a closer look at how to delete duplicate rows in SQL and walk you through the process step by step.

First, let's start with a basic example. Suppose we have a table called 'penguins' that contains some data:

```sql

CREATE TABLE penguins(foo INT, bar VARCHAR(15));

INSERT INTO penguins VALUES (3, 'kowalski');

INSERT INTO penguins VALUES (1, 'skipper');

```

Now, let's add some duplicate rows to the table:

```sql

INSERT INTO penguins VALUES (3, 'kowalski');

INSERT INTO penguins VALUES (1, 'skipper');

```

As you can see, we now have duplicate rows in our table. To delete these duplicates, we'll need to use a SQL statement that combines two tables using a LEFT JOIN.

```sql

DELETE a FROM penguins_copy a

LEFT JOIN (

SELECT foo, MAX(baz) AS max_baz

FROM penguins_copy

GROUP BY foo

) b ON a.foo = b.foo AND a.baz < b.max_baz;

```

In this statement, we're using the LEFT JOIN to combine our original table with an aggregate query that selects the maximum baz value for each foo group. We then delete any rows from the original table where the baz value is less than the maximum value.

But what if we don't have a timestamp or unique column to sort by? In this case, we can use a SQL statement that creates a new table with only the unique rows:

```sql

CREATE TABLE penguins_copy AS (

SELECT foo, bar, baz

FROM penguins

);

```

Then, we can simply copy the data back into our original table:

```sql

INSERT INTO penguins (foo, bar, baz)

SELECT foo, bar, baz

FROM penguins_copy;

```

And finally, we can drop the temporary table:

```sql

DROP TABLE IF EXISTS penguins_copy;

```

So there you have it - a step-by-step guide to deleting duplicate rows in SQL. Whether you're working with timestamps or not, these tips should help you keep your data tidy and your queries running smoothly.

Delete duplicate rows in sql