0

I have a table analyst_request

   -------------------------
    id |  test | request_id |
    -------------------------
    1  |   a   |NDQA201404320
    -------------------------
    2  |   a   |NDQA201404320    
    -------------------------
    3  |   b   |NDQA201404333  
   -------------------------
    4  |   b   |NDQA201404333

How do i just delete a any duplicate of the record containing 'NDQA201404320' and leave only one record of the same, suggestions.

Dharman
  • 30,962
  • 25
  • 85
  • 135
alphy
  • 291
  • 3
  • 19
  • http://stackoverflow.com/questions/14046355/how-do-i-delete-all-the-duplicate-records-in-a-mysql-table-without-temp-tables – favoretti Jan 15 '15 at 14:47

3 Answers3

2

Generally you can do

delete from analyst_request
where id not in
(
   select min(id) 
   from analyst_request
   where request_id IN ( 'NDQA201404320')
   group by test, request_id 
)

But MySQL can't delete from the same table you are selecting from. So you need to make a temp table like this

delete from analyst_request
where id not in
(
   select * from 
   (
      select min(id) 
      from analyst_request
      where request_id IN ( 'NDQA201404320')
      group by test, request_id 
   ) tmp
)
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • What if i want to reference a certain request_id, not the records in general? – alphy Jan 15 '15 at 14:51
  • You mean if you just care about duplicate `request_id` and not the `test` column? Then remove the `test` column from the query and you will be fine. – juergen d Jan 15 '15 at 14:53
  • What i mean is the delete is one at a time, i can choose to delete where there is a duplicate test='a' then next time where there is test='b', so I would like to know how to apply WHERE='a' or 'b' while deleting duplicate values – alphy Jan 15 '15 at 14:58
  • The query runs for all records. I don't understand what you want to do. If you have a rule for how to choose the duplicates then that can be implemented probably. – juergen d Jan 15 '15 at 15:04
1

Why not make it a unique field and prevent duplicates getting into the data base in the first place?

Ramzi Khahil
  • 4,932
  • 4
  • 35
  • 69
1

I can't comment to juergen d's answer but if you wanted to reference a certain request_id, then you would add that as a WHERE clause in the inner select statement like so

select min(id) 
from analyst_request
where request_id IN ( 'NDQA201404320', 'NDQA201404333' )
group by test, request_id

Hope that helps you and I understood your question correctly. Other than that, I think juergen d's answer is correct.

punsandguns
  • 69
  • 3