MySQL Forums
Forum List  »  Oracle

Re: Delete all rows in table but keep the last recent 2
Posted by: yvonne kire
Date: September 24, 2009 04:42PM

sort of right - this give me the 2 newest rows I like to keep for one user


Now I have millions of user_id where this would have to be applied to so I need to write some reoccur ant loop that takes care of it in one run without me interacting...

+-------------------------+-------------------+
| id | user_id | from_unixtime(create_date) |
+-------------------------+-------------------+
|28866 | 1978 | 2009-09-23 13:56:41 |
|28850 | 1978 | 2009-09-23 10:15:55 |
+-------------------------+-------------------+

I have the id that would be kept so any id other than this two should be deleted for this particular user_id.

Now - HOW do I use this info to DELETE all other rows that are older than the two I found?? ie any rows other than this two needs to be purged out from the table.
I store away this two values in a variable? and then delete from table where id not in (@variable1,@variable2) ?
or
[untested]
delete from tab where id in (select id from tab where user_id = XXX order by CREATE_DATE desc limit 2 );

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Delete all rows in table but keep the last recent 2
6054
September 24, 2009 04:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.