CODEWITHSUNDEEP
sqlite
theGreenCabbage
theGreenCabbage

Reputation: 4845

Removing duplicate row from SQLite3

I have a SQLite3 DB with ~10,000 rows of data.

The schema is like this:

id, stock_id, body, stock_created_at, symbol, updated_at, created_at

The PK is id with no FK

You can tell there is a duplicate if the stock_id is duplicated.

I have a MySQL query like this that I used to use, but it's for MySQL and not SQLite:

DELETE FROM stocks WHERE id NOT IN
    (SELECT MAX(id) FROM table GROUP BY stock_id);

Upvotes: 0

Views: 1064

Answers (1)

zedfoxus
zedfoxus

Reputation: 37029

Using SQLite3 (SQLite version 3.8.10.2 2015-05-20 18:17:19):

sqlite> create table test (id int, stock_id int, symbol text);
sqlite> insert into test values (1, 1, 'GOOG');
sqlite> insert into test values (2, 1, 'GOOGL');
sqlite> insert into test values (3, 2, 'AAPL');

sqlite> select * from test;
id          stock_id    symbol
----------  ----------  ----------
1           1           GOOG
2           1           GOOGL
3           2           AAPL

sqlite> delete from test 
   ...> where id in (
   ...>  select max(id) from test 
   ...>  group by stock_id 
   ...>  having count(*) > 1
   ...> );

sqlite> select * from test;
id          stock_id    symbol
----------  ----------  ----------
1           1           GOOG
3           2           AAPL

If stock table was a completely different table, the same concept works quite well:

sqlite> create table stock (stock_id int);
sqlite> insert into stock values (1);
sqlite> insert into stock values (2);
sqlite> insert into stock values (3);
sqlite> delete from stock
   ...> where stock_id in (
   ...>   select max(id) from test
   ...>   group by stock_id
   ...>   having count(*) > 1
   ...> );
sqlite> select * from stock;
stock_id
----------
1
3

Does this kind of query work for you? If not, what SQLite version are you using and can you edit your question with example data?

Upvotes: 2

Related Questions