Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL delete all results having count(*)=1

I have a table taged with two fields sesskey (varchar32 , index) and products (int11), now I have to delete all rows that having group by sesskey count(*) = 1. I'm trying a fews methods but all fails.

Example:

delete from taged where sesskey in (select sesskey from taged group by sesskey having count(*) = 1)

The sesskey field could not be a primary key because its repeated.

like image 255
Alex Avatar asked Dec 14 '09 16:12

Alex


3 Answers

DELETE  si
FROM    t_session si
JOIN    (
        SELECT  sesskey
        FROM    t_session so
        GROUP BY
                sesskey
        HAVING  COUNT(*) = 1
        ) q
ON      q.sesskey = si.sesskey

You need to have a join here. Using a correlated subquery won't work.

See this article in my blog for more detail:

  • Keeping rows
like image 194
Quassnoi Avatar answered Oct 26 '22 21:10

Quassnoi


Or if you're using an older (pre 4.1) version of MySQL and don't have access to subqueries you need to select your data into a table, then join that table with the original:

CREATE TABLE delete_me_table (sesskey varchar32, cur_total int);

INSERT INTO delete_me_table SELECT sesskey, count(*) as cur_total FROM orig_table
WHERE cur_total = 1 GROUP BY sesskey;

DELETE FROM orig_table INNER JOIN delete_me_table USING (sesskey);

Now you have a table left over named delete_me_table which contains a history of all the rows you deleted. You can use this for archiving, trending, other fun and unusual things to surprise yourself with.

like image 34
coffeepac Avatar answered Oct 26 '22 21:10

coffeepac


The SubQuery should work

 Delete from taged 
  Where sesskey in 
     (Select sesskey 
      From taged 
      Group by sesskey 
      Having count(*) = 1)

EDIT: Thanks to @Quassnoi comment below... The above will NOT work in MySql, as MySql restricts referencing the table being updated or deleted from, in a Subquery i you must do the same thing using a Join ...

like image 23
Charles Bretana Avatar answered Oct 26 '22 21:10

Charles Bretana