Hey. I was wondering if anyone knows how to do an sql call to delete all but the latest 50.000 rows in a certain table. Thanks.
what db system you have? in mysql 4.1+ use subqueries something similar : delete from table where id in (select id from table order by id desc limit 0,50000) Provide more information about table, maybe a table description I hope I'm right
If your table is really huge, I will copy the latest 50 entries to a temporary table and then issue truncate table <table_name> and then copy the 50 entries from temporary table back to original table. Otherwise provide us the schema of your table, we may help you with exact query to delete all but latest 50 entries. Hope this helps.
If your table has millions of records, Deleting with subqueries may not finish couple of hours. And also again if you have couple of millions records deleting may not finish hours because delete will delete records one by one. (This morning I tried to delete 1.2 million records on 3 million record table with an indexed field. It finished in 2 hours. (xeon 3 ghz - 2 cpu with 4 gb ram server) Best and fastest way is what link_dev is offered. First make a copy of table1 as table2 than; Query1: Insert Into Table2 select * from Table1 order by id desc limit 50000 Query2: Truncate table1 Query3: Insert Into Table1 select * from Table2 order by id Query4: Truncate table2 Query5: Drop table2 PS: In every step check your tables that your queries work perfectly. Clark
Okay here is the database: INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (7, 1162, 1163905722); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (8, 1356, 1163907407); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (9, 395, 1163908101); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (10, 777, 1163908114); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (11, 398, 1163909635); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (12, 1383, 1163910640); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (13, 459, 1163911429); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (14, 231, 1163911959); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (15, 231, 1163911959); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (16, 297, 1163911963); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (17, 231, 1163911969); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (18, 231, 1163911972); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (19, 231, 1163911975); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (20, 231, 1163911979); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (21, 231, 1163911982); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (22, 231, 1163911984); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (23, 231, 1163911991); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (24, 231, 1163911998); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (25, 231, 1163912004); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (26, 231, 1163912007); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (27, 231, 1163912016); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (28, 231, 1163912016); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (29, 231, 1163912021); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (30, 1380, 1163912021); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (31, 231, 1163912036); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (32, 231, 1163912047); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (33, 231, 1163912063); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (34, 231, 1163912063); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (35, 965, 1163912066); INSERT INTO `sed_link_clicks_ingoing` (`slc_id`, `sl_id`, `slc_time`) VALUES (36, 108, 1163912066); So I was thinking - maybe delete everything older than 14 days?
Well you'd first need to get the limiting timestamp, so if you're using PHP for example I'd do: // Delete records older than this number of days $limitDays = "14"; // Connect to database $conn = mysql_connect ('localhost', 'mysql_user', 'mysql_password'); $db = mysql_select_db('db_name', $conn); // Get current timestamp $time = time(); // Limiting timestamp $limitTS = $time - ($limitDays * 24 * 60 * 60); // Now setup SQL query $sql = "DELETE FROM `sed_link_clicks_ingoing` WHERE `slc_time` < `$limitTS`"; $result = mysql_query ($sql , $conn); if ($result){ print "Records older than $limitDays successfully deleted!"; } else { print "Error deleting records!"; } PHP:
delete from table dohhh where time < 324324234234; should also work... will take some time of course.