LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 02-24-2013, 04:13 PM   #1
sonia102d
LQ Newbie
 
Registered: Sep 2012
Posts: 18

Rep: Reputation: Disabled
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
 
Old 02-24-2013, 05:46 PM   #2
jefro
Moderator
 
Registered: Mar 2008
Posts: 16,676

Rep: Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455Reputation: 2455
What database are you using or any? Do any of the contents of files relate to others in some way?
 
Old 02-24-2013, 07:22 PM   #3
shivaa
Senior Member
 
Registered: Jul 2012
Location: Grenoble, Fr.
Distribution: Sun Solaris, RHEL, Ubuntu, Debian 6.0
Posts: 1,800
Blog Entries: 4

Rep: Reputation: 286Reputation: 286Reputation: 286
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.

Code:
~$ man split
~$ split --help
~$ split -b 100M filename
In your case, you can first split the file seq1 in small chunks, and then can use only first part to rebuild your db.

Code:
~$ split -b 100M seq1 new_seq1

~$ ls -la
............ seq1 
............ new_seq1aa
............ new_seq1ab
............ new_seq1ac

~$ du -sh new_seq1aa
100M
You can now use new_seq1aa as I will contain first 100M of the seq1 file.

Last edited by shivaa; 02-24-2013 at 07:25 PM.
 
1 members found this post helpful.
Old 02-25-2013, 06:17 PM   #4
sonia102d
LQ Newbie
 
Registered: Sep 2012
Posts: 18

Original Poster
Rep: Reputation: Disabled
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?

Last edited by sonia102d; 02-25-2013 at 06:25 PM.
 
Old 02-25-2013, 06:42 PM   #5
sonia102d
LQ Newbie
 
Registered: Sep 2012
Posts: 18

Original Poster
Rep: Reputation: Disabled
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
 
Old 02-25-2013, 06:51 PM   #6
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,374

Rep: Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383
Now we know it is in MySQL, that makes a difference.
You might want to check this, but
Code:
delete from tablename
where seq_id != 'seq1';
Substitute the table name for the real one.

Trim seq1 recs
Code:
delete from tablename
where  stop_id<=1000000 ;
You could combine those cmds into one if you really wanted, using an OR operator, but simpler/safer this way.

Quote:
command to check the count size of a file.
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/
 
1 members found this post helpful.
Old 02-25-2013, 07:09 PM   #7
sonia102d
LQ Newbie
 
Registered: Sep 2012
Posts: 18

Original Poster
Rep: Reputation: Disabled
"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 ;
 
Old 02-25-2013, 07:20 PM   #8
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,374

Rep: Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383Reputation: 2383
Note the operator is " != ", which means not equal.
Read that link I added.
 
1 members found this post helpful.
Old 02-28-2013, 09:52 AM   #9
sonia102d
LQ Newbie
 
Registered: Sep 2012
Posts: 18

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by shivaa View Post
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.

Code:
~$ split -b 100M seq1 new_seq1

~$ ls -la
............ seq1 
~$ du -sh new_seq1aa
100M
You can now use new_seq1aa as I will contain first 100M of the seq1 file.
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

Last edited by sonia102d; 02-28-2013 at 11:45 AM.
 
Old 02-28-2013, 07:28 PM   #10
shivaa
Senior Member
 
Registered: Jul 2012
Location: Grenoble, Fr.
Distribution: Sun Solaris, RHEL, Ubuntu, Debian 6.0
Posts: 1,800
Blog Entries: 4

Rep: Reputation: 286Reputation: 286Reputation: 286
Quote:
Originally Posted by sonia102d View Post
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?
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?
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
File content delete ajkannan83 Linux - Software 4 07-01-2008 04:16 AM
delete file-content ovince Programming 6 04-17-2007 03:44 AM
Delete text content syseeker Programming 5 02-07-2006 07:32 AM
How does one delete directories with content as the SU? Ferrell Ramey Linux - General 2 08-22-2002 07:16 PM
How to delete a dir and all it's content? ReSync Linux - Newbie 2 06-29-2002 06:45 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 10:46 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration