怎么写sql语句？ [英] How to write sql statement?

问题描述

i有一个带有'位置'列的sql表有值



tvm，kochi，india



如何删除一个特定值，例如： - kochi然后列将包含



tvm，印度





谢谢

Hi,

i have a sql table with a column ''location'' have values

tvm,kocindia

how to delete one specific value eg:- kochi and then column will contain

tvm,india


thanks

推荐答案

delete from mytable where location='kochi'

请参阅w3schools.com上的SQL DELETE语句 [ ^ ]。



（在仔细阅读了问题之后...... ：rolleyes :)

[更新]

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

(after having read carefully the question... :rolleyes:)
[update]

update mytable set location='' where location='kochi'

[/ update]

[/update]

正如CPallini所说，你使用了一个删除语句。我想补充一点，如果您的查询是由外部应用程序控制的，那么您应该尽可能使用参数化查询。看到你没有说出你正在使用什么数据库，这里是一个在SQL Server中使用存储过程的例子：

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

Quote：

我想删除位置列中的特定值而不是整行

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

如果您打算通过sql操作它们，那么在字段中存储逗号分隔值是不明智的。这是正常的形式（3NF），因此几乎没有支持正确地做到这一点。

你没有指定RDBSM - SQL是一个标准，仅此而已 - 因此确切的答案是：你不能。

让我们假设您使用的是Microsoft SQL Server - 因此您可以。

最好的选择是制作一个.net集成的数据类型。



最快的答案是：更新表设置位置=替换（位置，''kochi' '，''''）

但在你的情况下它会变成： tvm ,, india （注意双逗号）。如果它是可接受的，那就是它。

如果没有，你也可以发出另一个DML语句来减少双逗号： update table set location = replace（位置 '' ,, ''， ''， ''）。现在另一个问题是：如果值是另一个的子串，你可能会做出错误的删除。

您也可以使用正则表达式，但默认情况下也会丢失。



我建议使用特殊符号而不是逗号分隔： [tvm] [kochi] [india] 。因此，你不会有这样的危险，删除就像这样简单：更新表设置位置=替换（位置，''[kochi]''，''''）



还有其他选项，但让我们看看你真正想要/能做什么。

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.

这篇关于怎么写sql语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！