Delete db records from shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Delete db records from shell script
# 1  
Old 10-02-2015
Delete db records from shell script

Hello Mates,

I would request your help in a shell script,

simply I need to delete some matching db table records (rows) to ones in a given file:


Code:
------------------------------
#!/bin/bash
SQL="delete from numberlist where msidn='';"

MYSQL_USER="<your-user>"
MYSQL_PASS="<your-password>"
MYSQL_DB="<your-db>"

echo $SQL | /usr/bin/mysql --user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB
------------------------

file.txt like:

Code:
12345
23456
34567
45678
...

a few tousands records at most in file txt. How could I fetch those records from a file in sql script from a shell script?

thanks in advance
Best Regards,
# 2  
Old 10-02-2015
How about
Code:
/usr/bin/mysql --user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB <<EOF
$(awk '{print "delete from numberlist where msidn=" sq $1 sq ";"}' sq="'" file)
EOF

# 3  
Old 10-02-2015
Sorry about that mysql, I'm working on sqlplus of oracle Smilie

so is it something like the following? Didn't think about providing awk the file which includes numbers; brilliant

Code:
------
#!/usr/bin/ksh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=prsscm1

cd /path/to/oracle
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus -s user/password <<EOF
$(awk '{print "delete from numberlist where msidn=" sq $1 sq ";"}' sq="'" file)
EOF

# 4  
Old 10-02-2015
Why don't you do a non-destructive test run with e.g. select?
This User Gave Thanks to RudiC For This Post:
# 5  
Old 10-02-2015
I would suggest using SQL Loader to load the content of your file into a temp table and use the temp table later for deletion:-
Code:
#!/bin/ksh

DB_CONN_STR=${user}/${password}@${dbinstance}

### Creating table: TMP_MSISDN for loading...
SqlOut=`${ORACLE_HOME}/bin/sqlplus -s $DB_CONN_STR << EOF
create table TMP_MSISDN ( MSISDN  NUMBER(12) );
exit
EOF`

### Creating SQL loader control file...
{
        print "Load data"
        print "infile \"file.txt\""
        print "into table TMP_MSISDN"
        print "fields terminated by \",\""
        print "trailing nullcols"
        print "(MSISDN)"
} > loader.control

### Running SQL Loader...
${ORACLE_HOME}/bin/sqlldr $DB_CONN_STR control=loader.control 1> /dev/null 2> /dev/null

This User Gave Thanks to Yoda For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script matching similar records

hello all, I have requirement to identify similar records matching about 80% to 90%.I have to black list customers with multiple accounts. The data is in the Oracle Database, but is there any way I can get the data into flat file and compare the strings and fetch similar matching records? ... (2 Replies)
Discussion started by: kashik786
2 Replies

2. Shell Programming and Scripting

Extract top 20 records from sybase db in shell script

Hi, I am new to advanced scripting. I need to get top 20 records from database (Sybase) with a condition that all Char/varchar columns should have more than 4 characters. I need to do this via shell scripting, I have got half logic (on how to get varchar/char columns and stored it in a... (2 Replies)
Discussion started by: Selva_2507
2 Replies

3. Shell Programming and Scripting

Shell script for insert multiple records into a Database

I have a table in an Informix DB into which I want to insert multiple records at a time. Data for one of the column should be unique & other column data may be the same for all the records I insert Typical Insert Statement I use to insert one row : insert into employee(empid, country, state)... (5 Replies)
Discussion started by: nisav
5 Replies

4. Shell Programming and Scripting

Shell script to print date and no.of records

hi all, i want script to print the output in the following format filename yyyy/mm/dd count where count= no.of records in the file Thanks in advance hemanthsaikumar (11 Replies)
Discussion started by: hemanthsaikumar
11 Replies

5. Shell Programming and Scripting

count and compare no of records in bash shell script.

consider this as a csv file. H,0002,0002,20100218,17.25,P,barani D,1,2,3,4,5,6,7,8,9,10,11 D,1,2,3,4,5,6,7,8,9,10,11 D,1,2,3,4,5,6,7,8,9,10,11 D,1,2,3,4,5,6,7,8,9,10,11 D,1,2,3,4,5,6,7,8,9,10,11 T,5 N i want to read the csv file and count the number of rows that start with D and... (11 Replies)
Discussion started by: barani75
11 Replies

6. Shell Programming and Scripting

Help with shell script in formatting the records.

I have a text file in the following format. can any one please help me in printing the output in userfriendly format mentioned below. Input. 1) /ss/abc/1/w/s/domainname/abc1/logname/ ########error################### ########error################### ########error###################... (2 Replies)
Discussion started by: vinny81
2 Replies

7. Shell Programming and Scripting

need shell script to read particular records from a file

i am reading an i/p file input.txt as below and want to read all filenames as in highlighted in bold below and put them in a different file output.txt. can someone help me with a shell script to do this? thanks in advance regards brad input.txt --------- START TYPE:OPT INIT_SEQ:01... (8 Replies)
Discussion started by: bradc
8 Replies

8. UNIX for Dummies Questions & Answers

update records in a file using shell script...

Hi, I have a file with 6 columns. First 3 columns together make unique record. I have a variable ($v) which hold a value that is obtained by a caliculaion. I have to replace value in 5th columnn with the value of the variable ($v). The value $v is caliculated from col4 and col6 values. ... (2 Replies)
Discussion started by: new_learner
2 Replies

9. Shell Programming and Scripting

how to add, updat, and delete records using shell

thax for replying (5 Replies)
Discussion started by: sarah2009
5 Replies

10. Shell Programming and Scripting

Script needed to select and delete lower case and mixed case records

HELLO ALL, URGENTLY NEEDED A SCRIPT TO SELECT AND DELETE LOWER AND MIXED CASE RECORDS FROM A COLUMN IN A TABLE. FOR EXAMPLE : Table name is EMPLOYEE and the column name is CITY and the CITY column records will be: Newyork washington ... (1 Reply)
Discussion started by: abhilash mn
1 Replies
Login or Register to Ask a Question