CODEWITHSUNDEEP
mysqlsqlsql-delete
mechanicum
mechanicum

Reputation: 709

Delete from table according to count of attribute

Lets say I have a column named source in a table x. Individual entries can be like;

Id   c1   c2      source ...
1    a     b      something
2    b     a      something  
3    a     b      somethingelse
4    c     a      somethingelse
5    a     b      something
6    b     c      something

How can I delete entries with less than 3 same elements in source? For example since source value somethingelse appears 2 times, I need all entries that have somethingelse removed.

Upvotes: 3

Views: 128

Answers (4)

s.bandara
s.bandara

Reputation: 5664

DELETE FROM x WHERE id IN
( SELECT id FROM
  ( SELECT id, COUNT(source) AS n
      FROM x GROUP BY source 
      HAVING n < 3
  )
)

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

DELETE  id
FROM    yourtable a
JOIN    (
        SELECT  *
        FROM    yourtable
        GROUP BY
                source
        HAVING  COUNT(*) > 3
        ) b
ON      a.id = b.id

Upvotes: 0

BigMike
BigMike

Reputation: 6873

Roughly something like this would do the job

DELETE FROM TABLE_T1 WHERE ID IN (
   SELECT ID FROM TABLE_T1 GROUP BY SOURCE HAVING COUNT(*) < 3
)

Upvotes: 0

John Woo
John Woo

Reputation: 263713

DELETE  a
FROM    tableName a
        INNER JOIN
        (
            SELECT  source
            FROM    tableName
            GROUP BY SOURCE
            HAVING COUNT(*) < 3
        ) b ON a.source = b.source

One more thing to do for faster performance, add an INDEX to column SOURCE.

Upvotes: 4

Related Questions