SQL更新查询-你不能指定列名吗?

Is it possible to write a update statement and not give the columns names. For example

是否可以编写一个update语句而不给出列名称。例如

UPDATE tbl VALUES('1','2','3','4') WHERE id = 1;

The number of values will always match the column count.

值的数量将始终与列计数匹配。

Thanks in advance.

提前谢谢。

EDIT

编辑

I don't know the column names only the number of columns.

我不知道列名，只知道列数。

I know i could delete the row and then do an insert be then the id(which is A_I) wont be the same.

我知道我可以删除这一行，然后进行插入，那么id(即A_I)就不一样了。

Please help.

请帮助。

6 个解决方案

#1

You can retrieve the list of columns using:

您可以使用以下方法检索列列表:

SELECT  COLUMN_NAME 
FROM    INFORMATION_SCHEMA.COLUMNS C 
WHERE   TABLE_NAME = 'MyTable'

That allows you to build a new SQL query which checks for a value in each column. It's best to do this in a client, like a C#, Python or perl script.

这允许您构建一个新的SQL查询，该查询将检查每个列中的值。最好是在客户机中这样做，比如c#、Python或perl脚本。

#2

No, it is not possible:

不，不可能:

UPDATE Syntax

更新的语法

If you don't know the column names (which is rather strange), you could query the information schema:

如果您不知道列名(这很奇怪)，可以查询信息模式:

INFORMATION_SCHEMA Tables

INFORMATION_SCHEMA表

#3

...know i could delete the row and then do an insert be then the id(which is A_I) wont be the same...

…我可以删除行，然后进行插入，那么id(也就是A_I)就不会一样了……

Assuming that your first column is an AUTO_INCREMENT NOT NULL.

假设第一列是AUTO_INCREMENT NOT NULL。

INSERT into tbl VALUES(NULL, val1, val2);

#4

Even if it is possible, why would you want to do that? If you want to use the same query form for updates and inserts you can use "replace into tbl_name (column_names...) values (values...)"

即使有可能，你为什么要这么做?如果您想要使用相同的查询表单来更新和插入，您可以使用“replace into tbl_name (column_names…)值(值…)”

#5

Don't ever try to do that.

千万别这么做。

That will make your code hard to read and impossible to maintain.
If you later change the columns of your table all your code will fall apart and since you don't have the column names in the code you will not be able to search for the changed columns and find where they are used.

这将使您的代码难于阅读和无法维护。如果稍后更改表的列，那么所有代码都会崩溃，而且由于代码中没有列名，您将无法搜索已更改的列并找到它们的使用位置。

#6

I'm not sure if it's possible in a simple SQL query. However, if you can create a stored procedure then you have options. I'm not going to research the options, but here's the example.

我不确定在一个简单的SQL查询中是否可能。但是，如果您可以创建一个存储过程，那么您就有了选项。我不打算研究这些选项，但这里有一个例子。

I know that Oracle has metadata that you can query, so you can get the column names of a table. (I think SQL server has something similar)

我知道Oracle具有可以查询的元数据，因此可以获得表的列名。(我认为SQL server也有类似的功能)

So, since you can query this metadata then you can loop through the column names, build a string with column names for your update statement and do an "execute" of that string

因此，既然您可以查询此元数据，那么您可以遍历列名，为update语句构建一个带有列名的字符串，并执行该字符串的“execute”。

This has issues though: - security - SQL injection is possible since you are building a string to execute. Perhaps you could use bound variables or parameterize even the string you build and run - if you build a "hard-coded" UI, the moment the table changes and doesn't have default values for new columns or the columns are reordered, it will break. However, if you build the UI to read the metadata and then do the same thing to create the string to run then it should be fine.

这有一些问题:-安全性- SQL注入是可能的，因为您正在构建一个要执行的字符串。也许您可以使用绑定变量或者参数化您构建和运行的字符串—如果您构建一个“硬编码”的UI，当表发生变化并且新列没有默认值或者列被重新排序时，它就会崩溃。但是，如果构建UI来读取元数据，然后创建要运行的字符串，那么应该没问题。

#1