Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi,
 
i have a sql table with a column 'location' have values
 
tvm,kochi,india
 
how to delete one specific value eg:- kochi and then column will contain
 
tvm,india
 

thanks
Posted 30-Jan-13 22:52pm
hasbina1.2K
Comments
Zoltán Zörgő at 31-Jan-13 5:09am
   
I am confused: these values are in a single field ("cell"), or every one in a different row?
hasbina at 31-Jan-13 5:28am
   
single cell
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

delete from mytable where location='kochi'
 
See "SQL DELETE Statement" at w3schools.com[^].
 
(after having read carefully the question... Roll eyes | :rolleyes: )
[update]
update mytable set location='' where location='kochi'
[/update]
  Permalink  
v2
Comments
hasbina at 31-Jan-13 5:11am
   
i want to delete a particular value from the location column not the full row. read the question carefully..
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

As CPallini has said, you use a delete statement. I would add, if your query is being controlled by an external application, then you should use a parameterised query if possible. Seeing that you haven't said what database you are using, here's an example using a Stored Procedure in SQL Server:
CREATE PROCEDURE DeleteFromPlace
 @Location NVARCHAR(30)
AS
 DELETE FROM Place
 WHERE Location = @Location
  Permalink  
Comments
hasbina at 31-Jan-13 5:16am
   
@Pete
i want to delete a particular value from the location column not the full row.
Pete O'Hanlon at 31-Jan-13 5:30am
   
Ah, I see. You could use string manipulation - most dialects of SQL support this, and as you haven't said what database it is, we're limited in how much more information we could supply. Solution 4 is a reasonable answer.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Quote:
i want to delete a particular value from the location column not the full row

It is not wise to store comma separated values in a field if you intend to manipulate them via sql. It is against the normal form (3NF), and thus there are little support to do that correctly.
You have not specified the RDBSM - SQL is a standard, nothing more - thus the exact answer would be: you can not.
Let's assume you use Microsoft SQL Server - thus you can.
The best option would be to make a .net integrated data type for that.
 
The quickest answer would be: update table set location = replace(location,'kochi','')
But in your case it would become: tvm,,india (remark the double commas). If it is acceptable, than that's it.
If not, you could also issue an other DML statement too to reduce double commas: update table set location = replace(location,',,',','). And now an other problem: if the value is substring of an other you could make wrong deletions.
You could also use regular expressions, but that's also missing by default.
 
What I am suggesting is using a special notation instead of comma separation: [tvm][kochi][india]. Thus you would not have such hazards, and the deletion would be as simple as this: update table set location = replace(location,'[kochi]','')
 
There are other options too, but let's see what you really want/can do.
  Permalink  
Comments
hasbina at 31-Jan-13 5:31am
   
@Zoltán Zörgő
sir,thank you so much....
Zoltán Zörgő at 31-Jan-13 5:34am
   
You are welcome
hasbina at 31-Jan-13 5:47am
   
sir,
 
i tried to change.but didnt add the value in column when i write sql as below,
 
UPDATE STS_VEHICLE_INFORMATION
SET VD_ROUTENAME=ISNULL(VD_ROUTENAME,'')+'['+@ROUTENAME+']'
WHERE VD_VEHICLENUMBER = @VEHICLENUMBER AND VD_ROUTENAME IS NULL
Zoltán Zörgő at 31-Jan-13 5:54am
   
Wait a minute.
Zoltán Zörgő at 31-Jan-13 5:59am
   
Check this approach: update t set v = isnull('['+replace(v,',','][')+']' ,'')
SChristmas at 31-Jan-13 5:56am
   
Nice..
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

delete from test_table where location location not in ('tvm','india')
  Permalink  
Comments
SChristmas at 31-Jan-13 5:46am
   
the whole data get deleted
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

Use below query,
 
DECLARE @String_Original nvarchar(50)
DECLARE @Replacement2 nvarchar(50)
DECLARE @Pattern2 nvarchar(50)
 
set @String_Original = 'tvm,kochi,india'
set @Pattern2 = 'kochi,'
set @Replacement2 = ''
 
SELECT REPLACE(@String_Original,@Pattern2,@Replacement2)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 470
1 mhegazy94 460
2 Ravi Bhavnani 190
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,135
1 OriginalGriff 6,801
2 CPallini 5,350
3 George Jonsson 3,619
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web01 | 2.8.140922.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100