CODEWITHSUNDEEP
mysqlsql
user2848810
user2848810

Reputation: 1225

Delete all rows excepts the ones returned from the query

I've mistakenly been inserting duplicates into a DB. I've built up the query that returns back all of the row ids I want to keep. How do I delete the rows not given in the query. Can you please include the full query in your answer.

select min(matches.`id`)from matches
where  (stream_match_time, track_match_time, user_id, track_id ) in (
  select stream_match_time, track_match_time, user_id, track_id 
  from   matches
  group  by stream_match_time, track_match_time, user_id, track_id 
  having count(*) > 1
)
group  by stream_match_time, track_match_time, user_id, track_id 

Upvotes: 0

Views: 38

Answers (1)

forpas
forpas

Reputation: 164064

You can use:

delete from matches where id not in (...)

but you must nest your query one level deeper like this:

delete from matches
where id not in (
  select id from (
    select min(id) id from matches
    where  (stream_match_time, track_match_time, juked_user_id, track_id ) in (
      select stream_match_time, track_match_time, juked_user_id, track_id 
      from   matches
      group  by stream_match_time, track_match_time, juked_user_id, track_id 
      having count(*) > 1
    )
    group by stream_match_time, track_match_time, juked_user_id, track_id 
  ) t
)

I think that your query does not need 2 levels of aggregation, so try also this:

delete from matches
where id not in (
  select id from (
    select min(id) id 
    from   matches
    group by stream_match_time, track_match_time, juked_user_id, track_id 
    having count(*) > 1
  ) t
)

Upvotes: 2

Related Questions