Login or Register to Ask a Question and Join Our Community


UNIX and Linux Applications

How to delete a data starting with a phrase in a table - SQL?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Special Forums UNIX and Linux Applications How to delete a data starting with a phrase in a table - SQL?
# 1  
Old 05-17-2016
How to delete a data starting with a phrase in a table - SQL?
Hello,
I am trying to remove some rows in a table, which are including a phrase at a defined column but i could not find the unique result for this.

What I wish to do is to remove all lines including http://xx.yy at link column

Code:
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| id |   userid | statusid  |   name   | link                                                 	| notes   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+
|  1 |        1 |         1 | sql-TEST | ["http:\/\/google.com","http:\/\/xx.aa.bb"]		| test1	  | 
|  2 |        1 |         1 | sql-TEST | ["http:\/\/xx.yy.zz","http:\/\/google.fr"]		| test2   | 
|  3 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb","http:\/\/xx.aa.bb"]		| test3   | 
|  4 |        1 |         2 | sql-TEST | ["http:\/\/nbc.aa.bb","http:\/\/xx.yy.bb"]		| test4   | 
|  5 |        1 |         2 | sql-TEST | ["http:\/\/tt.aa.bb","http:\/\/xx.aa.bb"]		| test5   | 
|  6 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb","http:\/\/xx.yy.bb"]		| test6   | 
|  7 |        1 |         2 | sql-TEST | ["http:\/\/www.cnn.com"]				| test7   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+


Table name is mytable,

I tried below command but no joy:

Code:
delete from mytable where link like '%["http:\/\/xx.yy%' ;

Maybe as datas in related cells were seperated by "," it is not functioning..

Expected result should be:

Code:
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| id |   userid | statusid  |   name   | link                                                 	| notes   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+
|  1 |        1 |         1 | sql-TEST | ["http:\/\/google.com","http:\/\/xx.aa.bb"]		| test1	  | 
|  2 |        1 |         1 | sql-TEST | ["http:\/\/google.fr"]					| test2   | 
|  3 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb"]					| test3   | 
|  4 |        1 |         2 | sql-TEST | ["http:\/\/nbc.aa.bb"]					| test4   | 
|  5 |        1 |         2 | sql-TEST | ["http:\/\/tt.aa.bb","http:\/\/xx.aa.bb"]		| test5   | 
|  6 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb"]					| test6   | 
|  7 |        1 |         2 | sql-TEST | ["http:\/\/www.cnn.com"]				| test7   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+

Thanks in advance
Boris
# 2  
Old 05-17-2016
Not clear, your requirements are contradictory: you want "to remove all lines" (= rows in DB speak?), but in your expected result, the lines are still there but substrings of the link column have been have been eliminated.
And, the statement "it is not functioning" doesn't help either to determine what you're after.

For the second interpretation (based on your exp. result), you'll neet to UPDATE the table, not DELETE FROM it.
# 3  
Old 05-17-2016
Quote:
Originally Posted by RudiC
Not clear, your requirements are contradictory: you want "to remove all lines" (= rows in DB speak?), but in your expected result, the lines are still there but substrings of the link column have been have been eliminated.
And, the statement "it is not functioning" doesn't help either to determine what you're after.

For the second interpretation (based on your exp. result), you'll neet to UPDATE the table, not DELETE FROM it.
I'm sorry for the headache. I mean, i just need to remove each matching phrase from that each cell in each row but some cells have more than one data.
I'll try to do it with update command.


b.regards
Boris
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Phrase XML with Huge Data

HI Guys, I have Big XML file with Below Format :- Input :- <pokl>MKL=1,FN=1,GBNo=B10C</pokl> <d>192</d> <d>315</d> <d>35</d> <d>0,7,8</d> <pokl>MKL=1,dFN=1,GBNo=B11C</pokl> <d>162</d> <d>315</d> <d>35</d> <d>0,5,6</d> <pokl>MKL=1,dFN=1,GBNo=B12C</pokl> <d>188</d> (4 Replies)
Discussion started by: pareshkp
4 Replies

2. Solaris

SQL QUERY to Table Output

Hi I am trying to run sql query from solaris in csh script and send the output to email. Below is my sql query select p.spid,se.program seprogram, se.machine, se.username, sq.sql_text,sq.retrows from v$process p inner join v$session se on p.addr = se.paddr inner join ( select... (2 Replies)
Discussion started by: tharmendran
2 Replies

3. Shell Programming and Scripting

Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email. spool table_update.html; SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription'; spool off; exit; Please use code tags next time for your code and data.... (9 Replies)
Discussion started by: Sharanakumar
9 Replies

4. Homework & Coursework Questions

Write a shell script for SQL loader to load data into a staging table

Hi, I'm new to Linux. I'm working on a database, and need to load data in a database table (which I already created) using shell script. The table has two columns - Acct_number (not nullable) and date (timestamp). I'm not able to write a shell script for that. Can any one help me? ... (3 Replies)
Discussion started by: saisudeep
3 Replies

5. Shell Programming and Scripting

How to find a phrase and pull all lines that follow until the phrase occurs again?

I want to burst a report by using the page number value in the report header. Each section starts with *PAGE NO:* 1 Each section might have several pages, but the next section always starts back at 1. So I want to find the "*PAGE NO:* 1" value and pull all lines that follow until "*PAGE NO:* 1"... (4 Replies)
Discussion started by: Scottie1954
4 Replies

6. Shell Programming and Scripting

Delete lines starting with these strings

Platform : RHEL 5.8 I have text file called myapplication.log . In this file, I have around 800 lines which start with the followng three strings PWRBRKER-3493 PWRBRKER-7834 SCHEDULER-ERROR How can I delete these lines in one go ? (13 Replies)
Discussion started by: omega3
13 Replies

7. Shell Programming and Scripting

delete lines starting with a pattern

i have a file sample.txt containing i want to delete lines starting with 123 neglecting spaces and tabs. but not lines containing 123. i.e. i want files sample.txt as help me thanxx (4 Replies)
Discussion started by: yashwantkumar
4 Replies

8. Shell Programming and Scripting

How to get full sql table data using shell script

i have a Oracle table like col1 col2 ---- ----- a 1 b 2 c 3 when i write a script for it , it gives me all the data in one column. Please give me the solution which gives the exact result like we see in sql editors. for a in `echo " set feedback off; set pagesize 40;... (1 Reply)
Discussion started by: ss135r
1 Replies

9. Shell Programming and Scripting

How can i delete a keyword starting with x in unix

I am trying to delete key word starting with x in a unix text file. example, I am trying to delete the words like xaa,xabxbb,xbd and so on.... my input file is some thing like this xaaa w 1234 5678 rwsd ravi xw123 xbc3 ohrd want to delete words xaaa,xw123 and xbc3 from the above... (10 Replies)
Discussion started by: rdhanek
10 Replies

10. Shell Programming and Scripting

Delete lines starting with XX or YY or ZZ or ....

Hi There! My final task for today is to delete lines starting with certain numbers for e.g., my text block is and i want to delete all lines starting with 11 or 17 or 21 I know i can use multiple sed commands like sed '/^11,/d' <filename> sed '/^17,/d' <filename> sed '/^21,/d'... (2 Replies)
Discussion started by: orno
2 Replies
Login or Register to Ask a Question