Explanation of a select statement

Databases
1

Hi All.

I am trying to delete duplicates from an SQLite Database, and found something that works, but I am not sure why.

Can someone explain it?

SELECT * FROM Pets
WHERE EXISTS (
  SELECT 1 FROM Pets **p2** 
  WHERE Pets.PetName = p2.PetName
  AND Pets.PetType = p2.PetType
  AND Pets.rowid > p2.rowid
);

I don’t understand the p2 item.

How does this work?

Sorry if this is a silly question.

Regards

2

When something like “p2” is placed after the table name in a query (or JOIN), p2 becomes an alias for that table.

Even though you have two queries for the same table, the alias sorta treats the second reference to the Pets table like it’s a separate table so the WHERE clause does not get confused with things like Pets.PetType = Pets.PetType. So now Pets.PetType = p2.PetType is not pointing at the same column of the same record in the same table, if that makes sense.

3

Try this

DELETE FROM Pets WHERE rowId NOT IN (SELECT MIN(rowId) FROM Pets GROUP BY PetName, PetType)

Make a backup first :stuck_out_tongue:

4

Yikes! Did you know that not all databases use consecutive row ids???

1 Like
5

Greg there are tons of things I don’t know about SQLite / Databases. I’m learning every day!

6

I was replying to @Eduard_Schlecht with his suggestion of SELECT MIN(rowId) FROM Pets

Doing that and assuming that you use MIN(rowId) is a great way to delete records that you still want on systems that don’t always increment auto IDs

7

Ok.

Not offended. Not hurt. Just admitting I don’t know everything, Greg.

Have a great day!

Michael

8

I am not sure that if I find 3 dogs named Rex, any of them is a duplicate, except if the owner is the same.