I need to delete multiple table data in different SGDB types, but I do not know how to do this in a practical way.
I need to keep 10 records in the tables and I need to delete all the remaining ones, but I can not do this with the command: DELETE FROM tabela WHERE id > 10 Because some tables do not exist in sequence, there are tables that start with ID 101, for example, but I have to preserve the top 10.
I'll need this in Firebird, PostgreSQL and SQL Server.
This response does not consider primary or foreign keys and can keep records without references in other tables.
I can do this as follows:
When I do the query (in PostgreSQL for example):
SELECT * FROM tabela
ORDER BY id
LIMIT 10
I am selecting the first 10 first records (with the LIMIT 10 ) of the table sorted by ID (with ORDER BY id ), so what I need is to delete the records that are not in this list, here comes the use of NOT IN .
Using the query:
SELECT * FROM tabela
WHERE id NOT IN (SELECT id FROM tabela ORDER BY id LIMIT 10)
I'm selecting all records that did not appear in the previous query, notice that I'm doing this because the id column is the primary key and can not have duplicate records, also when I do the "subselect" I'm not putting SELECT * and yes SELECT id since I need the list of all id's that are not in the previous query, knowing which id's are not in the previous column I could easily select them as follows:
SELECT * FROM tabela
WHERE id NOT IN (11,12,13,14,...)
But I do not know how to use subselect.
To delete then just use the command DELETE in that list that is not in the first 10 id's:
DELETE FROM tabela
WHERE id NOT IN (SELECT id FROM tabela ORDER BY id LIMIT 10)
In Firebird I limit the result with ROWS :
DELETE FROM tabela
WHERE id NOT IN (SELECT id FROM tabela ORDER BY id ROWS 10)
In SQL Server, I limit with TOP :
DELETE FROM tabela
WHERE id NOT IN (SELECT TOP 10 id FROM tabela ORDER BY id)