Delete db records from shell script

Shell Programming and Scripting
1

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:

------------------------------
#!/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:

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

How about

/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

Sorry about that mysql, I'm working on sqlplus of oracle :slight_smile:

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

------
#!/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

Why don't you do a non-destructive test run with e.g. select ?

1 Like
5

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

#!/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
1 Like