Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Delete if condition met in a column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Delete if condition met in a column
# 1  
Old 04-08-2013
Delete if condition met in a column
i have a table like this:

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

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

Code:
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  
Old 04-08-2013
Your select might be returning multiple rows. May be you can change like

Code:
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  
Old 04-08-2013
Thank you, but there are no record deleted

this is the output
Code:
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  
Old 04-08-2013
Can you print the results here for me?

Code:
SELECT FROM inbox WHERE senderNumber IN (SELECT senderNumber FROM blacklist WHERE senderBlock='Y')

# 5  
Old 04-08-2013
Code:
sed 's:^0:+61:'

This User Gave Thanks to RudiC For This Post:
jazzyzha
# 6  
Old 04-08-2013
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:

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

Quote:
Originally Posted by PikK45
Can you print the results here for me?

Code:
SELECT FROM inbox WHERE senderNumber IN (SELECT senderNumber FROM blacklist WHERE senderBlock='Y')

change script to:
Code:
#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:
Code:
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  
Old 04-08-2013
Try this

Code:
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'"
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk - print when condition is met

I have a file.txt containing the following: Query= HWI-ST863:386:C5Y8UACXX:3:2302:16454:89688 1:N:0:ACACGAAT Length=100 Score E Sequences producing significant alignments: (Bits) Value ... (2 Replies)
Discussion started by: tons92
2 Replies

2. Shell Programming and Scripting

Add another condition to bash for when not met

In the below I can not seem to add a line that will add Not low if the statement in bold is not true or meet. I guess when the first if statement is true/meet then print low, otherwise print Not low in $(NF + 1). I am not sure how to correctly add this. Thank you :). if(low <= $2 && $2 <=... (5 Replies)
Discussion started by: cmccabe
5 Replies

3. Shell Programming and Scripting

Need help on how to append on the filename when condition met.

Hi All, Seeking for your assistance on how to append the specific string when $3 condion met. ex. file1.txt ar0050046b16,5,888,0,0,0,0.00,0.00,0.00,0.00,25689.55 ar0050046b16,5,0,0,0,0,0.00,0.00,0.00,0.00,25689.55 ar0050046b16,5,0,0,0,0,0.00,0.00,0.00,0.00,25689.55 expected output:... (5 Replies)
Discussion started by: znesotomayor
5 Replies

4. Shell Programming and Scripting

Getting the records once condition met

Hi All, Seeking for your assistance to get the records once the $2 met the condition. Ex. file 1.txt 123455,10-Aug-2020 07:33:37 AM,2335235,1323534,12343 123232,11-Aug-2015 08:33:37 PM,4234324,1321432,34364 Output: 123455,10-Aug-2020 07:33:37 AM,2335235,1323534,12343 What i did... (5 Replies)
Discussion started by: znesotomayor
5 Replies

5. Shell Programming and Scripting

Comparing all lines in a column with another is condition is met

Sorry for this noob question, I have file with 4 columns like where columns 2 and 4 have numbers a 55 k 3 b 59 l 3 c 79 m 277 d 255 n 277 e 257 o 267 f 267 p 287 g 290 q 287 h 290 r 287 i 310 s 900 now i want to select only those rows, where values in column 4 are greater than... (4 Replies)
Discussion started by: amits22
4 Replies

6. Shell Programming and Scripting

Read column from file and delete rows with some condition..

Hi.... I have a need of script to do delete row whenever condition is true.... 2.16 (3) 1 3 9999 0 (1) (0) 34.42 (4) 1 3 9999 37 (2) (3) 34.38 (4) 1 3 9999 64 (2) (3) 34.4 (4) 1 3 1 ... (13 Replies)
Discussion started by: nex_asp
13 Replies

7. UNIX for Advanced & Expert Users

While loop only if a condition is met

All, I wrote the following section of code (which logically in PHP would of worked): tmpPATH=${1} tmpTAG=${2} if then while read tmpTAG tmpPATH do fi echo $tmpTAG echo $tmpPATH if then done < ./config.cfg fi (4 Replies)
Discussion started by: Cranie
4 Replies

8. Shell Programming and Scripting

do nothing if condition is not met but not exit

Hello all, I created the below script....and it seemed to be working fine. My problem is i want the script to ignore rest of the things if my condition is not met but do not exit.... #!/bin/ksh ########################### ########################### # Set name of the listener, this... (2 Replies)
Discussion started by: abdul.irfan2
2 Replies

9. Shell Programming and Scripting

sed/awk to update 1st column if condition met

Hi, I am trying to update the 1st column of a file but only if it contains a char here is an example of my file 1111aaa 9999 textaaa 22222bbb 9999 textbbb 3333 9999 textccc 444ddd 9999 textddd i would like the output to remove any characters () from... (5 Replies)
Discussion started by: plennon
5 Replies

10. Shell Programming and Scripting

How to break a loop if condition is met

I am having trouble figuring this code I want to grep a text from a file and if it match certain text it break out of the loop or it should continue searching for the text Here is what I have written but it isn't working while true f=`grep 'END OF STATUS REPORT' filename` do if ... (9 Replies)
Discussion started by: Issemael
9 Replies
Login or Register to Ask a Question