如何更新记录的所有列，而不必列出每列

I'm trying to find out if there is a way to update a record without having to list every column name that is to be updated.

我试图找出是否有办法更新记录而不必列出要更新的每个列名。

For instance, it would be nice if I could use something similar to:

例如，如果我可以使用类似的东西，那将是很好的：

// the parts inside braces are what I am trying to figure out
UPDATE Employee
SET {all columns, without listing each of them} 
WITH {this record with id of '111' from other table}
WHERE employee_id = '100'

If this can be done, what would be the most straightforward/efficient way of writing such a query?

如果可以这样做，那么编写此类查询的最直接/最有效的方法是什么？

4 个解决方案

#1

It's not possible.

这是不可能的。

What you're trying to do is not part of SQL specification and is not supported by any database vendor. See the specifications of SQL UPDATE statements for MySQL, Postgresql, MSSQL, Oracle, Firebird, Teradata. Every one of those supports only below syntax:

您尝试做的不是SQL规范的一部分，任何数据库供应商都不支持。请参阅MySQL，Postgresql，MSSQL，Oracle，Firebird，Teradata的SQL UPDATE语句的规范。其中每一个仅支持以下语法：

UPDATE table_reference
   SET column1 = {expression} [, column2 = {expression}] ...
[WHERE ...]

#2

This is not posible, but..

这是不可能的，但..

you can doit:

你能行的：

begin tran
delete from table where CONDITION
insert into table select * from EqualDesingTabletoTable where CONDITION
commit tran

be carefoul with identity fields.

与身份领域密切相关。

#3

you could do it by deleting the column in the table and adding the column back in and adding a default value of whatever you needed it to be. then saving this will require to rebuild the table

你可以通过删除表中的列并重新添加列并添加默认值来实现它。然后保存这将需要重建表

#4

How about using Merge?

如何使用Merge？

https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

It gives you the ability to run Insert, Update, and Delete. One other piece of advice is if you're going to be updating a large data set with indexes, and the source subset is smaller than your target but both tables are very large, move the changes to a temporary table first. I tried to merge two tables that were nearly two million rows each and 20 records took 22 minutes. Once I moved the deltas over to a temp table, it took seconds.

它使您能够运行插入，更新和删除。另一条建议是，如果您要使用索引更新大型数据集，并且源子集小于目标，但两个表都非常大，请先将更改移动到临时表。我试图合并两个表，每个表近两百万行，20个记录需要22分钟。一旦我将增量移动到临时表，就需要几秒钟。

#1