delete random data in mysql

Question

I want to delete some random data from my table, and cut 90% rows (I have very large data, I just need a sample), table looks like this:

ID    |Trans_No    |Doctor_ID |Trans_Type                    |PM |Cost
12340 |10.853329   |          |ADMINISTRASI                  |   |0.00
12341 |10.853329   |1004      |JASA MEDIS                    |   |25000.00
12342 |10.853329   |          |OBAT RESEP FARMASI NO : 177   |F  |2000.00
12343 |10.836033   |          |ADMINISTRASI                  |   |0.00
12344 |10.836033   |1001      |JASA MEDIS                    |   |25000.00
12345 |10.836033   |          |OBAT RESEP FARMASI NO : 317   |F  |0.00
12346 |10.836032   |          |ADMINISTRASI                  |   |0.00
12347 |10.836032   |1004      |JASA MEDIS                    |   |25000.00
12348 |10.836032   |          |PEMERIKSAAN RADIOLOGI NO 092.1|R  |15000.00
12349 |10.836034   |1064      |JASA MEDIS                    |   |25000.00
12350 |10.836034   |          |PEMERIKSAAN RADIOLOGI NO 093.1|R  |20000.00

I think this query will work:

DELETE FROM my_table WHERE RAND() <= 0.9

but as you can see, some data has same trans_no, if one trans_no is deleted, the other data that has same trans_no should be deleted to, is there any query to do this?

Answer 1

You should select eligible trans_no first and then delete them, like this:

DELETE FROM my_table
WHERE trans_no IN (
    SELECT trans_no
    FROM (SELECT DISTINCT trans_no FROM my_table) x
    WHERE rand() <= 0.9
)