Unix/Linux Go Back    


UNIX and Linux Applications Discuss UNIX and Linux software applications. This includes SQL, Databases, Middleware, MOM, SOA, EDA, CEP, BI, BPM and similar topics.

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

UNIX and Linux Applications


Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 05-17-2016
baris35 baris35 is offline
Registered User
 
Join Date: Oct 2012
Last Activity: 10 September 2017, 7:05 PM EDT
Posts: 120
Thanks: 54
Thanked 5 Times in 5 Posts
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
Sponsored Links
    #2  
Old Unix and Linux 05-17-2016
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 22 September 2017, 6:20 AM EDT
Location: Aachen, Germany
Posts: 11,367
Thanks: 297
Thanked 3,523 Times in 3,242 Posts
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.
Sponsored Links
    #3  
Old Unix and Linux 05-17-2016
baris35 baris35 is offline
Registered User
 
Join Date: Oct 2012
Last Activity: 10 September 2017, 7:05 PM EDT
Posts: 120
Thanks: 54
Thanked 5 Times in 5 Posts
Quote:
Originally Posted by RudiC View Post
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
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Phrase XML with Huge Data pareshkp Shell Programming and Scripting 4 02-28-2016 08:14 AM
Shell scripting unable to send the sql query data in table in body of email Sharanakumar Shell Programming and Scripting 9 09-26-2014 09:50 AM
Write a shell script for SQL loader to load data into a staging table saisudeep Homework & Coursework Questions 3 09-26-2013 03:28 PM
How to find a phrase and pull all lines that follow until the phrase occurs again? Scottie1954 Shell Programming and Scripting 4 08-28-2013 06:20 PM
How to get full sql table data using shell script ss135r Shell Programming and Scripting 1 08-30-2011 08:32 AM



All times are GMT -4. The time now is 07:47 AM.