CODEWITHSUNDEEP
sql-serversql-server-2012
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

Delete 75% Of a Table Randomly

Trying to make a smaller sample database but still have the data be somewhat statistically relevant. How can I delete x % of rows from the table ? Been fooling around with the NEWID() function.

Upvotes: 7

Views: 1786

Answers (3)

M.Ali
M.Ali

Reputation: 69554

DELETE 
FROM TABLE_NAME
WHERE PK IN (SELECT TOP (75) PERCENT PK
             FROM TABLE_NAME
             ORDER BY NEWID())

Suggestion by Martin Smith

DELETE T 
FROM (SELECT TOP (75) PERCENT * 
      FROM TABLE_NAME 
      ORDER BY NEWID()) T

Upvotes: 10

gunn
gunn

Reputation: 320

My working example on Azure SQL Database (most syntax compatible with SQLServer):

delete TOP (75) PERCENT 
FROM outer_sources.del_me_tbl

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Another way would be:

DELETE 
FROM table_name
WHERE ABS(CHECKSUM(NEWID())) % 100 < 75 ;

It will not delete exactly 75% of the rows but it will be faster for a bigger table, as it won't need to do a sort.

Upvotes: 4

Related Questions