Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Comments
Zoltán Zörgő 31-Jan-13 5:09am    
I am confused: these values are in a single field ("cell"), or every one in a different row?
hasbina 31-Jan-13 5:28am    
single cell

SQL
delete from mytable where location='kochi'


See "SQL DELETE Statement" at w3schools.com[^].

(after having read carefully the question... :rolleyes:)
[update]
SQL
update mytable set location='' where location='kochi'

[/update]
 
Share this answer
 
v2
Comments
hasbina 31-Jan-13 5:11am    
i want to delete a particular value from the location column not the full row. read the question carefully..
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:
SQL
CREATE PROCEDURE DeleteFromPlace
 @Location NVARCHAR(30)
AS
 DELETE FROM Place
 WHERE Location = @Location
 
Share this answer
 
Comments
hasbina 31-Jan-13 5:16am    
@Pete
i want to delete a particular value from the location column not the full row.
Pete O'Hanlon 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.
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.
 
Share this answer
 
Comments
hasbina 31-Jan-13 5:31am    
@Zoltán Zörgő
sir,thank you so much....
Zoltán Zörgő 31-Jan-13 5:34am    
You are welcome
hasbina 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ő 31-Jan-13 5:54am    
Wait a minute.
Zoltán Zörgő 31-Jan-13 5:59am    
Check this approach: update t set v = isnull('['+replace(v,',','][')+']' ,'')
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)
 
Share this answer
 
delete from test_table where location location not in ('tvm','india')
 
Share this answer
 
Comments
Sandesh M Patil 31-Jan-13 5:46am    
the whole data get deleted

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900