phpmyadmin Question posted in
The official documentation can be found here.

SQL Query for mass deleting for no primary key table – Phpmyadmin

I have a SQL Table that doesn’t have a primary key but should have which is the account_id, key and index.

But unfortunately, I already have millions of entries before I was able to notice this problem.

I have account_id from 2000001 and upto around 2004000+ and also several keys which should’ve been primary keys so that only the value would be replaced if ever there is an update.

Sample image below,
https://i.imgur.com/g6je2zL.png

There should only be 1 #betaminutes and #online_minute under account_id 2000846 and the value should only be the highest

Is it possible to do a mass delete on each and every account_id for all the different keys I have and only save the entry with the highest value for each account_id’s keys?

Thank you

Tags:

2

Answers


  1. 0

    A large delete will be expensive, you are using. In MySQL, you can use:

    delete t
    from t join
         (select account_id, key, max(value) as max_value
          from t
          group by account_id, key
          having count(*) > 1
         ) tt
         using (account_id, key)
     where value < max_value;

    Note: If there are ties for the highest value, then this will keep the ties, but your sample data suggests that there are no ties.

    Login or Signup to reply.
  2. 0

    You can use the exists as follows:

    delete from your_table t
 where exists 
       (select 1 from your_Table tt
         where t.account_id = tt.account_id
           and t.key = tt.key
           and tt.value > t.value)
   -- use following condition if there is more than given key and you only want this query to operat on this two keys
   and t.key in ('#betaminutes', '#online_minute')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search