Remove duplicates from every tables in db

Question

I have a db with multiple tables. The tables have all the same structure with one column called date and a bunch of other columns with various data.

I can remove the duplicates from a single table as follow:

DELETE FROM table1
WHERE EXISTS (
    SELECT 1 FROM table1 p2
    WHERE table1.date = p2.date
    AND table1.ROWID > p2.ROWID
)

I have about 30 tables to clean regularly. Although I could run the above code for each table, is there a way to do this via a for loop after getting the list of tables in the db as:

SELECT name FROM sqlite_master WHERE type='table'

Vinith_jk_bro · Accepted Answer · 2023-11-07 09:31:19Z

2

Here's a script that generates & runs individual DELETE statements for each table: --> Loops through the table list and remove duplicates from each table

BEGIN;
 FOR table_name IN (SELECT * FROM table_list) DO
   EXECUTE IMMEDIATE 'DELETE FROM ' || table_name.name || ' WHERE ROWID NOT IN (
     SELECT MAX(ROWID) FROM ' || table_name.name || ' GROUP BY date
   )';
 END FOR;
COMMIT;

answered Nov 7, 2023 at 9:31

Vinith_jk_bro

915 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove duplicates from every tables in db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related