👤
Home Man
Search
Today's Posts
Register

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?

👤 Login to reply

 
Thread Tools Search this Thread
# 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 to reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Delete all lines starting with # character John K Shell Programming and Scripting 8 08-26-2017 05:40 PM
Phrase XML with Huge Data pareshkp Shell Programming and Scripting 4 02-28-2016 08:14 AM
SQL QUERY to Table Output tharmendran Solaris 2 03-17-2015 03:17 PM
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
Delete lines starting with these strings omega3 Shell Programming and Scripting 13 07-26-2013 11:56 AM
delete lines starting with a pattern yashwantkumar Shell Programming and Scripting 4 11-07-2011 01:19 AM
How to get full sql table data using shell script ss135r Shell Programming and Scripting 1 08-30-2011 08:32 AM
Delete lines starting with XX or YY or ZZ or .... orno Shell Programming and Scripting 2 11-13-2007 04:33 PM


All times are GMT -4. The time now is 08:37 AM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
×
UNIX.COM Login
Username:
Password:  
Show Password