Hi

I want to delete all data from a table in my database but keep the last 2 months there (newest entries)...

How can i do it?

Thanks

Lots of different ways. But, since you (again) do not provide any useful details, there is nothing we can tell you. We can't guess as to what kind of database you're talking about, version/distro of Linux, or how you want to do this, so what do you think we'll be able to tell you??? Doing this via an ksh script running on RHEL 5 is far different than doing the same operation via typing in a direct SQL command to an Oracle RAC server. Both accomplish the same thing, but are far different. Also, you don't say whether you want to do this once, or repeatedly...scripting something makes sense if you're going to do it over and over, but not much if it's one time.

Again, as you've been asked many times in the past, can you show us what you have done/tried so far to accomplish this??

I am using Centos 64bit 6.6 and MariaDB mysql v10.0.17 and i want to do this only once.

Thanks and sorry for not providing the info on my topic....

Database name: hal9000_insomniacs
Table name: inline_notifications
Table columns: notify_id, notify_to_id, notify_sent, notify_read, notify_title, notify_text, notify_from_id, notify_type_key, notify_url, notify_meta_app, notify_meta_area, notify_meta_id, notify_meta_key

Target is to keep the data from last 2 months (February, March, and from April the rest days)

Thanks

We don't like asking each time...please read the "Question Guidelines" link in my posting signature.
Ok...so your column names don't tell us anything, and if you want to purge older records, you will very obviously need one of those columns to have SOME sort of date in it. Are any of them dates?? What is the data type on those fields???

And, as you were asked previously, what have you done/tried/researched ON YOUR OWN so far???? Again, this is something that is easily looked up...the MySQL documentation covers deleting records, the syntax of the command, and the options.
...is one of the very easily-found examples. Please, again, try to show some effort of your own.

I have on those fields simple text and nothing like date or id that increase or something that i can tell to my command to use and delete until that point.....

Yes i found searching on Google the same command as you but as i do not have any date related info i post here.

The only thing that it may help is that i have there 2311323 total rows as phpmyadmin report for that table and maybe i can delete from 1 up to 2.000.000 ?

I know this is nt accurate at all as it is not per day that i was looking for but it will help me a lot ...

Then you are probably out of luck...

If you cannot describe to us how you determine the age of a record, then you probably cannot describe it to the DBMS, and neither we nor MariaDB are mind readers.

1 members found this post helpful.

There is nothing related to describe as i can't determine the age of any record....

It will be help me if i can from 2311323 total rows to delete from 1 up to 2.000.000 and leave there the rest....

But here you have the same problem... how do you identify the row ordering that would let you identify "row 1" or "row 2,000,000" and those in between?

Can you tell us the domain (definition) of the data in each column that might be used for such ordering?

If you have nothing that identifies a date, how do you think you'll be able to perform date functions???? And do you have any other tables? What software generated that database?

Again, you're omitting what you've done and tried, and are making us guess.

There is no any data that can help ordering so it seems that i am out of luck

Thanks anyway all of you !

If there is no data that establishes an ordering, then there is no magic way to infer such an ordering.

It is worth noting that in the relational database model (i.e. what MariaDB supports) there is, by definition, no inherent ordering of rows - they are a "set", and members of sets have no order. Any desired ordering must be based on pairing of some property of the members of some subset (i.e., some column value) with members of some other set (i.e., set of integers, range of timestamps, etc.). In the absence of some column based ordering, there is, by definition, no order to the rows (i.e., members of the set).

Ok i found something that can help

In the column notify_id it starts from 13652220 and increase up to 18661584

Some entries are missing for example but this i think it should not be a problem as if not exits just goes to the next one....

It is like.... 13652220, 13652221, 13652224, 13652227

So can i use this to delete from 13652220 up to 18000000 for example?

You can use that to delete the range of rows corresponding to those values of the notify_id column.

We cannot say whether that corresponds to some range of time values because we have no idea at all what domain those values belong to (i.e., what they mean).

If you assume that the numeric values of notify_id correspond to some monotinically increasing function of time then it might do what you want. But only you can know that - we still have no idea what those notify_id values mean or how they are generated or whether they are unique, etc.

If the application that uses the database depends on key integrity, it is possible that removing those values may break other things too. This possibility is also indicated by the fact that there are missing values - it raises questions such as were they there and removed, or were they never there? If they were removed, what process removed them and based on what criteria? If we manually remove others will some process fail because they do not exist? These are all questions related to key integrity and model rules.

I point this out instead of just saying, sure delete them, so that you will know that it might have unexpected effects.

And again, only you know what the application is and only you are able to explore answers to these questions. And obviously, it is you who must be responsible if something unexpected does happen...

My suggestion would be to spend some time learning the basics of the relational model, then trying to understand the application within the context of the relational model, and then making decisions about deleting data based on all that solid knowledge.

They are just notifications for members for example that you have one new pm.....

I can delete the old ones without any problem don't worry.

Can i use something like this to delete them?

or

Again, have you tried reading the MySQL documentation (as you were told to before), and look at the syntax of the delete command?? It took you longer to post this, than it would have taken you to actually TRY those commands and see if they worked. Again, you need to start showing effort of your own.

And again, as you were asked before, WHAT SOFTWARE generated this database??? Are there any other tables that may have dates in them??? Unless you answer questions asked of you and provide details, there isn't much point in posting.

:: EDIT ::
A brief Google search turns up that name, as part of the Sphinx software. Amazingly, it also has documentation on deleting records...did you TRY to look this up?
http://sphinxsearch.com/docs/current...phinxql-delete