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

Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
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  
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  
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 Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Phrase XML with Huge Data
pareshkp
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>... Shell Programming and Scripting
4
Shell Programming and Scripting
Shell scripting unable to send the sql query data in table in body of email
Sharanakumar
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....... Shell Programming and Scripting
9
Shell Programming and Scripting
Write a shell script for SQL loader to load data into a staging table
saisudeep
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? ...... Homework & Coursework Questions
3
Homework & Coursework Questions
How to find a phrase and pull all lines that follow until the phrase occurs again?
Scottie1954
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"...... Shell Programming and Scripting
4
Shell Programming and Scripting
How to get full sql table data using shell script
ss135r
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;...... Shell Programming and Scripting
1
Shell Programming and Scripting

Featured Tech Videos