Delete if condition met in a column

Shell Programming and Scripting
1

i have a table like this:

id, senderNumber, blacklist
-----------------------------
1   0835636326   Y
2   0373562343   Y
3   0273646833   Y

and I want to delete automatically if a new inserted row on another table consist anything on senderNumber column above using a BASH Script

I already using this script:

BLOCKLIST="$( mysql -uroot -pabcde smsd -N -s -r -e "SELECT senderNumber FROM blacklist WHERE senderBlock='Y'" | tr '\n' ' ')"
mysql -uroot -pabcde smsd -e "DELETE FROM inbox WHERE senderNumber = '$BLOCKLIST'"

and this the output:

sh -x /etc/autodelete.sh
+ tr \n  
+ mysql -uroot -pabcde smsd -N -s -r -e SELECT senderNumber FROM blacklist WHERE senderBlock='Y'
+ BLOCKLIST=083808034690 08164853500 
+ mysql -uroot -pabcde smsd -e DELETE FROM inbox WHERE senderNumber = '083808034690 08164853500 '

but no luck. any help would be very appreciated.

2

Your select might be returning multiple rows. May be you can change like

BLOCKLIST="$( mysql -uroot -pabcde smsd -N -s -r -e "SELECT senderNumber FROM blacklist WHERE senderBlock='Y'" | tr '\n' ',' | sed 's/,$//g' )"
mysql -uroot -pabcde smsd -e "DELETE FROM inbox WHERE senderNumber in ($BLOCKLIST)"
3

Thank you, but there are no record deleted

this is the output

sh -x /etc/autodelete.sh
+ sed s/,$//g
+ tr \n ,
+ mysql -uroot -pabcde smsd -N -s -r -e SELECT senderNumber FROM blacklist WHERE senderBlock='Y'
+ BLOCKLIST=083808034690,08164853500
+ mysql -uroot -pabcde smsd -e DELETE FROM inbox WHERE senderNumber IN (083808034690,08164853500)

---------- Post updated at 04:03 AM ---------- Previous update was at 03:53 AM ----------

My mistake, the new record will insert it by default with country code like +6183808034691 from 083808034691. how can i add the +61 and remove the zero?

thanks

4

Can you print the results here for me?

SELECT FROM inbox WHERE senderNumber IN (SELECT senderNumber FROM blacklist WHERE senderBlock='Y')
5 
sed 's:^0:+61:'
1 Like
6

Thanks RudiC, the country code are added and the zero are deleted, but the second number still not changed.

i've got the error message like this:

sh -x /etc/autodelete.sh
+ mysql -uroot -pabcde smsd -N -s -r -e SELECT senderNumber FROM blacklist WHERE senderBlock='Y'
+ sed s:^0:+61:
+ tr \n ,
+ BLOCKLIST=+6183808034690,08164853500,
+ mysql -uroot -pabcde smsd -e DELETE FROM inbox WHERE senderNumber IN (+6183808034690,08164853500,)
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

---------- Post updated at 04:20 AM ---------- Previous update was at 04:10 AM ----------

change script to:

#BLOCKLIST="$( mysql -uroot -pabcde smsd -N -s -r -e "SELECT senderNumber FROM blacklist WHERE senderBlock='Y'" | tr '\n' ',' | sed 's:^0:+61:' )"
mysql -uroot -pabcde smsd -e "SELECT FROM inbox WHERE senderNumber IN (SELECT senderNumber FROM blacklist WHERE senderBlock='Y')"

and got this error:

sh -x /etc/autodelete.sh
+ mysql -uroot -pabcde smsd -e SELECT FROM inbox WHERE senderNumber IN (SELECT senderNumber FROM blacklist WHERE senderBlock='Y')
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM inbox WHERE senderNumber IN (SELECT senderNumber FROM blacklist WHERE sende' at line 1
7

Try this

BLOCKLIST="$( mysql -uroot -pabcde smsd -N -s -r -e "SELECT senderNumber FROM blacklist WHERE senderBlock='Y'" | sed 's/^0/+61/g' | tr '\n' ',' | sed 's/,$//g' )"
mysql -uroot -pabcde smsd -e "DELETE FROM inbox WHERE senderNumber = '$BLOCKLIST'"
8

Thanks PikK45,

using this:

BLOCKLIST="$( mysql -uroot -pabcde smsd -N -s -r -e "SELECT senderNumber FROM blacklist WHERE senderBlock='Y'" | sed 's/^0/+61/g' | tr '\n' ',' | sed 's/,$//g' )"
mysql -uroot -pabcde smsd -e "DELETE FROM inbox WHERE senderNumber = '$BLOCKLIST'"

output:

sh -x /etc/autodelete.sh
+ tr \n ,
+ sed s/,$//g
+ sed s/^0/+61/g
+ mysql -uroot -pabcde smsd -N -s -r -e SELECT senderNumber FROM blacklist WHERE senderBlock='Y'
+ BLOCKLIST=+6183808034690,+618164853500
+ mysql -uroot -pabcde smsd -e DELETE FROM inbox WHERE senderNumber = '+6183808034690,+618164853500'

but still the record not deleted.

9

Dear jazzyzha,

I missed to add IN there.

mysql -uroot -pabcde smsd -e "DELETE FROM inbox WHERE senderNumber = '$BLOCKLIST'"

should be changed to

mysql -uroot -pabcde smsd -e "DELETE FROM inbox WHERE senderNumber IN ('$BLOCKLIST')"
1 Like
10

Thank you Brother PikK45 !, now it can delete the new record. :smiley: thank you so much for your help.