Selective delete in SQL


 
Thread Tools Search this Thread
# 1  
Selective delete in SQL

Hi All

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

Bascially I had built a database using a set of files
Code:
seq1 of 300 mb
seq2 of 200 mb
seq3 of 350 mb
seq4 of 300 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 seq4 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
Smilie
# 2  
What is the database(i.e.oracle, db2, etc.)?
Which tables in the database do you want to remove rows for?
What is the structure(i.e. columns, indexes) of these tables?
Which rows do you want to remove?
# 3  
Hi,

I thought about it and realized that i could delete all unwanted entries in the mysql databaseb 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


Code:
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 :

Code:
seq1 3333 66663
seq1 213267 723560
seq1 900000 1000000

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

---------- Post updated at 07:42 PM ---------- Previous update was at 07:24 PM ----------

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
# 4  
Yes, of course there is a way to perform DB operations.

What have you tried? What is the table structure?

What are your SQL queries you have executed and the results?
# 5  
I used :
"delete from tablename
where seq_id = 'seq2'
i deleted all the other sequences.

and after that
delete from tablename
where stop_id>=1000000 ;

i dont know how to reduce the original file size
its 300mb and i want to retain the first 100mb .

could some1 suggest?

---------- Post updated 02-26-13 at 07:59 PM ---------- Previous update was 02-25-13 at 09:54 PM ----------

hi
is there any command to count the number of bits in a file ?

Anything that i can use to retain the first 1000 or 100mb bits in a file and delte the remaining?

Last edited by sonia102; 02-26-2013 at 03:38 AM..
# 6  
There most likely is a way to find out what is in the first 100 mb of the file, but that is not how databases work. One reason database systems were invented is that you do not have to bother with this kind of things, so the documentation for this is hard to find and understand.
I could tell you how to do this for Oracle systems if I'd know the exact version, but there would be a LOT research you'd have to do and I'm not sure if recreating the database wouldn't be less straining on your resources.
A quick google search revealed that shrinking mysql datafiles is not a built in feature for that database system, but there are tools that can do it. The native approach would be to add a datafile of the desired size, move all data there and drop the old ones (and I guess that is what those tools do).
Edit: Using tools like vi or awk on database files usually is a bad idea and will most likely corrupt your database.

Last edited by cero; 02-27-2013 at 04:30 AM..
# 7  
Quote:
Originally Posted by sonia102
I used :
"delete from tablename
where seq_id = 'seq2'
i deleted all the other sequences.

and after that
delete from tablename
where stop_id>=1000000 ;

i dont know how to reduce the original file size
its 300mb and i want to retain the first 100mb .

could some1 suggest?

---------- Post updated 02-26-13 at 07:59 PM ---------- Previous update was 02-25-13 at 09:54 PM ----------

hi
is there any command to count the number of bits in a file ?

Anything that i can use to retain the first 1000 or 100mb bits in a file and delte the remaining?
Count the bytes and multiply by 8.

Seriously - I'd propose you learn some basics on databases.
Deleting anything from the outside will corrupt the database rendering it totally unusable.
Deleting sequences does not really save space - they are just metadata worth a few bytes, counters that have a start and end value, and e.g. a step width, all recorded in the DB management structures.
Retaining the "first" 100 MB (= deleting 200 MB above) is pointless as consistent data are spread all over tablespaces/files.

Do as cero proposed - create a new datafile/tablespace, move all relevant data there, and then drop the old structures. Or, seriously consider recreating.
 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Science: Mathematics
Difficulty: Medium
111,111,111 x 111,111,111 = 12,345,678,987,654,321
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. UNIX and Linux Applications

How to delete a data starting with a phrase in a table - SQL?

Hello, I am trying to remove some rows in a table, which are including a phrase at a defined column but i could not find the unique result for this. What I wish to do is to remove all lines including http://xx.yy at link column ... (2 Replies)
Discussion started by: baris35
2 Replies

3. UNIX for Dummies Questions & Answers

Selective replace and delete

Hi My input file looks like this: >BAHMI01000090.1 Details of the shopping list 9800 item00090, whole set of listed artifacts and objects >BAHMI01050012.1 Details of the shopping list 9800 item02310, whole set of listed artifacts and objects >BAHMI01070078.1 Details of the shopping list ... (5 Replies)
Discussion started by: sonia102
5 Replies

4. UNIX for Dummies Questions & Answers

Sql delete and append

Hi I have a question on appending and deleting entries in mysql table. This is my sample table. table name: details id_name | model | mode | media| first | end | id | level | +--------------------+-------+---------+-----+-------+-------+--------+--------+ | PSK_30s1207681L002 | -1 | 1... (7 Replies)
Discussion started by: rsi.245
7 Replies

5. UNIX for Dummies Questions & Answers

SQL selective replace

Hi I have a table which looks like this id | name | length | clone | null 1 | string 1 | 12345643 | string 1 | NULL | 2 | string 2 | 2345612 | string 2 | NULL | 3 | string 3 | 3421556 | string 3 | NULL | 4 | string 4 | 1236742 | string 4 | NULL | 5 | string 5 | 2312677 | string 5 | NULL |... (2 Replies)
Discussion started by: rsi.245
2 Replies

6. UNIX for Dummies Questions & Answers

[Solved] Selective delete command

Hi All. I didnt know how to put this question as but i want to delete my values following string_X but need to retain the data in it. I hope the following might help me in conveying my doubt. My sequence looks like this. >string_1 CAJW010000001... (3 Replies)
Discussion started by: sonia102
3 Replies

7. Shell Programming and Scripting

Capture rows for a column in file from delete sql -Oracle

Hi, This may not be the right forum but i am hoping someone knows an answer to this. I have to capture rows for a column that was deleted. How can i do that without having to write a select query? delete from myschema.mytable where currentdatetimestamp > columnDate this should delete 5... (4 Replies)
Discussion started by: jakSun8
4 Replies

8. Shell Programming and Scripting

RegEx - selective delete around a pattern

I need RegEx to delete text block delimited by "^--" and "^request saved" if the block contained the pattern "FAILED:No air,rail,hotel or car" in the following. Many thanks in advance! company_id=9292 queue_id=72 internationalOnly=0 Building XML... ABC123 Adding passenger first=First ... (1 Reply)
Discussion started by: roshansharma
1 Replies

9. UNIX for Dummies Questions & Answers

Help with selective ls

Hi all :wall: Can anyone advise how do I use ls to do a selective amd sorted listing of file that I want to have as below? Am looking for files that are named as log_<nnnn>.txt, where <nnnn> are numeric, i.e. I want to have a listing sorted from the newest to the oldest of files that... (7 Replies)
Discussion started by: newbie_01
7 Replies

10. UNIX Desktop Questions & Answers

Trying to delete a directory pattern-selective deletion

Trying to delete a directory or a file using a pattern-selective deletion (using * and ? ) (4 Replies)
Discussion started by: herberwz
4 Replies

Featured Tech Videos