delete content of databasee
 

Hi All

This might be a weird query but it is related to deleting specific details in database.

Basically I had built a database using a set of files
seq1 of 300 mb
seq2 of 200 mb
seq3 of 350 mb
seq4 of 300 mb
seq5 of 100 mb


and after building the database i realized that i didn't need the whole data. i am required to not only delete content of database related to seq2 to seq5 but also delete content related to last 200 bytes in seq1

Basically i am required to retain only first 100 mb of data in seq1.

without going through the whole procedure of rebuilding the db could i delete contents of database corresponding to these requirements.
i know its a little unusal but could some one give a direction.

Thanks

What database are you using or any? Do any of the contents of files relate to others in some way?

What type of database you have? If it's in text format, made up by combining different files, then you can use split cmd (see manual here) to split the files first, on basis of size or lenght and then can re-build your db.

In your case, you can first split the file seq1 in small chunks, and then can use only first part to rebuild your db.

You can now use new_seq1aa as I will contain first 100M of the seq1 file.

Hi,

I thought about it and realized that i could delete all unwanted entries in mysql database by using a select and delete command
i want to retain only those seq1 entries whose stop_id is less than or equal to 1000000( ie from 0 to 1000000 )

I want to delete all other sequences from seq2 to 13 and also other entries having name VCC and QAC


id start_id stop_id
seq1 3333 66663
seq1 213267 723560
seq1 900000 1000000
seq1 12348965 19968965
seq2 2333 5623
seq1 2033333 2279800
seq3 213267 723560
seq2 900000 1000000
QAC 203333 229800
seq1 4532113 7633115
VCC 33333 3421111

I want only seq1 in the database having stop id<=1000000 .

required o/p looks like :

seq1 3333 66663
seq1 213267 723560
seq1 900000 1000000

Is there a way to delete all entries other than that required?

Also another question is to reduce the file size. Is there any command to check the count size of a file.

I am asking this because I have to retain the first 100Mb of data in seq1 and then deleting the remaining bits in that file.

What command should I use to retain the first 100 mb of file and delete the remaining bits using vi or awk?

Sorry for so many posts .Thanks ALL for your help

Now we know it is in MySQL, that makes a difference.
You might want to check this, but
Substitute the table name for the real one.

Trim seq1 recs
You could combine those cmds into one if you really wanted, using an OR operator, but simpler/safer this way.

Use 'ls -l' to get a file size. note that to trim by file size, you'd have to do it at the file level, BEFORE inserting into DB. See split cmd above.

I suggest you bookmark and read the MYSQL docs; you're going to need them http://dev.mysql.com/doc/refman/5.1/en/

"delete from tablename
where seq_id != 'seq1'
"

wont this command delete my seq1 entries rather than deleting others?

Cant i have a delete command which will first delete all other seq entries first.
And once we have retained seq 1 entries only we can use this

delete from tablename
where stop_id>=1000000 ;

Note the operator is " != ", which means not equal.
Read that link I added.

Hi Shiva.

I used this method for my text file and it did split into 3 parts.
However newseq1aa, newseq1ab , newseq1ac has file size 104857600 ,104857600 and 96661504
du command gives me disk space 100 mb and 100mb and 93 mb but since the ls -lrt command gave me different sizes more than 100 mb is my method wrong?

Please do advise.
Thanks

It means file size was 306376704 bytes (104857600 b + 104857600 b + 96661504 b) i.e. about 292 mb. du means disk usage, so it's correct, whereas I doubt some calculation error in ls -lrt output. Can you post it's outout?