CODEWITHSUNDEEP
sqlitesubquery
b85411
b85411

Reputation: 9990

Trouble with Sqlite subquery

My CustomTags table may have a series of "temporary" records where Tag_ID is 0, and Tag_Number will have some five digit value.

Periodically, I want to clean up my Sqlite table to remove these temporary values.

For example, I might have:

Tag_ID      Tag_Number
0           12345
0           67890
0           45678
1           12345
2           67890

In this case, I want to remove the first two records because they are duplicated with actual Tag_ID 1 and 2. But I don't want to remove the third record yet because it hasn't been duplicated yet.

I have tried a number of different types of subqueries, but I just can't get it working. This is the last thing I tried, but my database client complains of an unknown syntax error. (I have tried with and without AS as an alias)

DELETE FROM CustomTags t1
WHERE t1.Tag_ID = 0
    AND (SELECT COUNT(*) FROM CustomTags t2 WHERE t1.Tag_Number = t2.Tag_Number) > 1

Can anyone offer some insight? Thank you

Upvotes: 1

Views: 45

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

There are many options, but the simplest are probably to use EXISTS;

DELETE FROM CustomTags
WHERE Tag_ID = 0
  AND EXISTS(
    SELECT 1 FROM CustomTags c
    WHERE c.Tag_ID <> 0 AND c.Tag_Number = CustomTags.Tag_Number
  )

An SQLfiddle to test with.

...or NOT IN...

DELETE FROM CustomTags
WHERE Tag_ID = 0
  AND Tag_Number IN (
    SELECT Tag_Number FROM CustomTags WHERE Tag_ID <> 0
  )

Another SQLfiddle.

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37029

With your dataset like so:

sqlite> select * from test;
tag_id      tag_number
----------  ----------
1           12345
1           67890
0           12345
0           67890
0           45678

You can run:

delete from test 
where rowid not in (
  select a.rowid 
  from test a 
  inner join (select tag_number, max(tag_id) as mt from test group by tag_number) b 
    on a.tag_number = b.tag_number 
    and a.tag_id = b.mt
);

Result:

sqlite> select * from test;
tag_id      tag_number
----------  ----------
1           12345
1           67890

Please do test this out with a few more test cases than you have to be entirely sure that's what you want. I'd recommend creating a copy of your database before you run this on a large dataset.

Upvotes: 0

Related Questions