CODEWITHSUNDEEP
mysqldelete-row
Darjeeling
Darjeeling

Reputation: 999

delete random data in mysql

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?

Upvotes: 1

Views: 1610

Answers (2)

Sahal
Sahal

Reputation: 4146

You have to have an inner query for this.

First you have to find the trans_no to be deleted

    SELECT 
            trans_no
    FROM 
           (SELECT DISTINCT trans_no FROM my_table)
    WHERE rand()

Then You have to have separate delete query like this 

DELETE FROM my_table WHERE trans_no IN (Put the above query here)

Finally

DELETE FROM my_table WHERE  trans_no IN (
        SELECT 
                trans_no
        FROM 
               (SELECT DISTINCT trans_no FROM my_table) as derived_table
        WHERE rand()
)

Upvotes: 2

mvp
mvp

Reputation: 116367

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
)

Upvotes: 3

Related Questions