Moving new row and deleting old row to another table


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Moving new row and deleting old row to another table
# 1  
Moving new row and deleting old row to another table

Hi, I want to move a new row to another table if the field from new row doesn't have the first word that I categorized (like: IRC blablabla, PTM blablabla, ADM blablabla, BS blablabla).

I already use this script but doesn't work as I expected.

Code:
CHECK_KEYWORD="$( mysql -uroot -p123456 smsd -N -s -r -e "SELECT sms_keyword FROM sms_key" | sed 's/^0/+62/g' | tr '\n' ',' | sed 's/,$//g' )"
mysql -uroot -p123456 smsd -e "INSERT INTO inbox_spam SELECT * FROM inbox WHERE TextDecoded NOT LIKE '($CEK_KEYWORD)%'"


the result are:

Code:
+ mysql -uroot -p123456 smsd -N -s -r -e SELECT sms_keyword FROM sms_key
+ sed s/^0/+62/g
+ sed s/,$//g
+ tr \n ,
+ CHECK_KEYWORD=IRC,PTM,ADM,BS
+ mysql -uroot -pgurlia001 smsd -e INSERT INTO inbox_spam SELECT * FROM inbox WHERE TextDecoded NOT LIKE '(IRC,PTM,ADM,BS)%'


please help and thank you for your guidance Smilie

Last edited by jazzyzha; 06-05-2013 at 01:36 AM..
# 2  
Sorry unable to understand what is not working..
# 3  
Hi, thanks for your repply.

The result is:
Code:
mysql -uroot -p123456 smsd -e INSERT INTO inbox_spam SELECT * FROM inbox WHERE TextDecoded NOT LIKE '(IRC,PTM,ADM,BS)%'


and i think the correct output should be like this (correct me if im wrong):

Code:
mysql -uroot -p123456 smsd -e INSERT INTO inbox_spam SELECT * FROM inbox WHERE TextDecoded NOT LIKE '(IRC%,PTM%,ADM%,BS%)'

so it will move the row (into inbox_spam table from inbox table) which had no first word like IRC, PTM, ADM, BS.

ps: sorry for my bad english

thank you

Last edited by jazzyzha; 06-05-2013 at 02:30 AM..
# 4  
have u tried changing your tr to
Code:
tr '\n' '%,'

# 5  
already changed it to:
Code:
CHECK_KEYWORD="$( mysql -uroot -p123456 smsd -N -s -r -e "SELECT sms_keyword FROM sms_key" | sed 's/^0/+62/g' | tr '\n' '%,' | sed 's/,$//g' )"
mysql -uroot -p123456 smsd -e "INSERT INTO inbox_spam SELECT * FROM inbox WHERE TextDecoded NOT LIKE '($CHECK_KEYWORD)'"

the result are without a comma now:


Code:
+ sed s/,$//g
+ tr \n %,
+ sed s/^0/+62/g
+ mysql -uroot -p123456 smsd -N -s -r -e SELECT sms_keyword FROM sms_key
+ CHECK_KEYWORD=IRC%PTM%ADM%BS%
+ mysql -uroot -pgurlia001 smsd -e INSERT INTO inbox_spam SELECT * FROM inbox WHERE TextDecoded NOT LIKE '(IRC%PTM%ADM%BS%)'

# 6  
use below code
Code:
 
 tr '\n' '#' | sed 's/#/%,/g'

Instead of
Code:
tr '\n' '%,'

This User Gave Thanks to pravin27 For This Post:
# 7  
Quote:
Originally Posted by jazzyzha
... I want to move a new row to another table if the field from new row doesn't have the first word that I categorized (like: IRC blablabla, PTM blablabla, ADM blablabla, BS blablabla).
...
Code:
mysql>
mysql>
mysql> select * from sms_key;
+-------------+
| sms_keyword |
+-------------+
| IRC         |
| PTM         |
| ADM         |
| BS          |
+-------------+
4 rows in set (0.00 sec)

mysql>
mysql> select * from inbox;
+------+----------------+
| x    | textdecoded    |
+------+----------------+
|    1 | IRC test1      |
|    2 | Hello, World!  |
|    3 | PTM test1      |
|    4 | Test ADM test1 |
+------+----------------+
4 rows in set (0.00 sec)

mysql>
mysql> select * from inbox_spam;
Empty set (0.00 sec)

mysql>
mysql>
mysql> insert into inbox_spam
    -> select *
    -> from inbox
    -> where textdecoded not regexp (
    ->                                select concat_ws ( '', '^(', group_concat(sms_keyword separator '|'), ').*' )
    ->                                from sms_key
    ->                              )
    -> ;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from inbox_spam;
+------+----------------+
| x    | textdecoded    |
+------+----------------+
|    2 | Hello, World!  |
|    4 | Test ADM test1 |
+------+----------------+
2 rows in set (0.00 sec)

mysql>
mysql>

This User Gave Thanks to durden_tyler For This Post:
Login or Register for Dates, Times and to Reply

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

Test Your Knowledge in Computers #911
Difficulty: Easy
In C, we can get the address of a variable as follows: pointer = &variable;
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Add Row from First Row (Split Row)

HI Guys, I have Below Input :- RepigA_hteis522 ReptCfiEtrBsCll_aofe MSL04_MSL2_A25_1A 0 9 MSL04_MSL2_A25_1B 0 9 MSL04_MSL2_A25_1C 0 9 RepigA ReptCfiEtrBsCll hteis522 aofe MSL04_MSL2_A25_1A 0 9 MSL04_MSL2_A25_1B 0 9 MSL04_MSL2_A25_1C 0 9 Split Data in two first row... (2 Replies)
Discussion started by: pareshkp
2 Replies

2. Shell Programming and Scripting

awk to convert table-by-row to matrix table

Hello, I need some help to reformat this table-by-row to matrix? infile: site1 A:o,p,q,r,s,t site1 C:y,u site1 T:v,w site1 -:x,z site2 A:p,r,t,v,w,z site2 C:u,y site2 G:q,s site2 -:o,x site3 A:o,q,s,t,u,z site3 C:y site3 T:v,w,x site3 -:p,routfile: SITE o p q r s t v u w x y... (7 Replies)
Discussion started by: yifangt
7 Replies

3. Shell Programming and Scripting

In php, Moving a new row to another table and deleting old row

Hi, I already succeed moving a new row to another table if the field from new row doesn't have the first word that I categorized (like: IRC blablabla, PTM blablabla, ADM blablabla, BS blablabla). But it can't delete the old row. Please help me with the script. my php script: INSERT INTO... (2 Replies)
Discussion started by: jazzyzha
2 Replies

4. Shell Programming and Scripting

deleting dupes in a row

Hello, I have a large database in which name homonyms are arranged in a row. Since the database is large and generated by hand, very often dupes creep in. I want to remove the dupes either using an awk or perl script. An input is given below The expected output is given below: As can be... (2 Replies)
Discussion started by: gimley
2 Replies

5. UNIX for Dummies Questions & Answers

Finding files with one row then moving them

Hi guys can you please help me with a script to find files with one row the move the file to another directory the files are in /optima/prd/optdir/ZTE_BSS/Combiner/ZTE_2G/out/BSC_PS_Basic_Meas and I want to move them to /optima/prd/optdir/ZTE_BSS/Loader/error/ZTE_2G/BSC_PS_Basic_Meas ... (5 Replies)
Discussion started by: Dj Moi
5 Replies

6. UNIX for Dummies Questions & Answers

Shell Script: Traverse Database Table Row by Row

Hello Everyone, My issue is that I want to traverse a database table row by row and do some action on the value retrieved in each row. I have gone through a lot of shell script questions/posts. I could find row by row traversal of a file but not a database table. Please help. Thanks &... (5 Replies)
Discussion started by: ahsan.asghar
5 Replies

7. Shell Programming and Scripting

Moving data from a specified column/row to another column/row

Hello, I have an input file like the following: 11_3_4 2_1_35 3_15__ _16989 Where '_' is a space. The data is in a table. Is there a way for the program to prompt the user for x1,y1 and x2,y2, where x1,y1 is the desired number (for example x=6 y=4 is a value of 4) and move to a desired spot... (2 Replies)
Discussion started by: jl487
2 Replies

8. UNIX for Dummies Questions & Answers

deleting a row if a certain column is below a certain number

How can you delete a row if a certain column is bigger than a certain number? I have the following input: 20080709 20081222 95750 1 0 0.02 94.88 20080709 20081222 95750 2 0 0.89 94.88 20080709 20081222 9575 1 0 0 94.88 20080709 20081222 9575 2 0 0 94.88 20080709 20081222 9587.5 1 0 0... (6 Replies)
Discussion started by: Pep Puigvert
6 Replies

9. UNIX for Dummies Questions & Answers

deleting a row if a certain column is below a certain number

How can you delete a row if a certain column is bigger than a certain number? I have the following input: 20080709 20081222 95750 1 0 0.02 94.88 20080709 20081222 95750 2 0 0.89 94.88 20080709 20081222 9575 1 0 0 94.88 20080709 20081222 9575 2 0 0 94.88 20080709 20081222 9587.5 1 0 0... (1 Reply)
Discussion started by: Pep Puigvert
1 Replies

10. Shell Programming and Scripting

Deleting all occurences of a duplicate row

Hi, I need to delete all occurences of the repeated lines from a file and retain only the lines that is not repeated elsewhere in the file. As seen below the first two lines are same except that for the string "From BaseLine" and "From SMS".I shouldn't consider the string "From SMS" and "From... (7 Replies)
Discussion started by: ragavhere
7 Replies

Featured Tech Videos