How to write sql statement?

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:52

hasbina807

Add a Solution

Comments

Zoltán Zörgő - 31 Jan '13 - 5:09

I am confused: these values are in a single field ("cell"), or every one in a different row?

hasbina - 31 Jan '13 - 5:28

single cell

5 solutions

Top Rated
Most Recent

Solution 4

Accept Solution Reject Solution

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

Posted 30 Jan '13 - 23:25

Zoltán Zörgő39.6K

Comments

hasbina - 31 Jan '13 - 5:31

@Zoltán Zörgő sir,thank you so much....

Zoltán Zörgő - 31 Jan '13 - 5:34

You are welcome

hasbina - 31 Jan '13 - 5:47

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:54

Wait a minute.

Zoltán Zörgő - 31 Jan '13 - 5:59

Check this approach: update t set v = isnull('['+replace(v,',','][')+']' ,'')

SChristmas - 31 Jan '13 - 5:56

Nice..

Solution 1

Accept Solution Reject Solution

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

Posted 30 Jan '13 - 23:00

CPallini245K

Edited 30 Jan '13 - 23:18

Comments

hasbina - 31 Jan '13 - 5:11

i want to delete a particular value from the location column not the full row. read the question carefully..

Solution 3

Accept Solution Reject Solution

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

Posted 30 Jan '13 - 23:07

Pete O'Hanlon487K

Comments

hasbina - 31 Jan '13 - 5:16

@Pete i want to delete a particular value from the location column not the full row.

Pete O'Hanlon - 31 Jan '13 - 5:30

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.

Solution 6

Accept Solution Reject Solution

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

Posted 30 Jan '13 - 23:43

$andesh M Patil20.7K

Solution 5

Accept Solution Reject Solution

delete from test_table where location location not in ('tvm','india')

Permalink

Posted 30 Jan '13 - 23:39

AdityaPratapSingh1.7K

Comments

SChristmas - 31 Jan '13 - 5:46

the whole data get deleted

Add a Solution

Add your solution here

OK Paste as

Strip HTML
Encode HTML
Paste as-is
Code block
Quoted Text
Best guess

To display as

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I've read and agree to the Terms of Use and Privacy Policy
Please subscribe me to the CodeProject newsletters

Submit your solution!

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.

Let's work to help developers, not make them feel stupid.

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

0	OriginalGriff	335
1	Sergey Alexandrovich Kryukov	185
2	Mohammed Hameed	123
3	Ron Beyer	99
4	Richard MacCutchan	89

0	Sergey Alexandrovich Kryukov	8,286
1	OriginalGriff	6,561
2	CPallini	3,533
3	Rohan Leuva	2,703
4	Maciej Los	2,234

		9,879,772 members (43,372 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using

Interested
Ignored