SQL 如何更新记录的所有列而不必列出每一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29926765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to UPDATE all columns of a record without having to list every column
提问by BinaryCat
I'm trying to find out if there is a way to update a record without having to list every column name that needs to be updated.
我试图找出是否有一种方法可以更新记录而不必列出需要更新的每个列名。
For instance, it would be nice if I could use something similar to the following:
例如，如果我可以使用类似于以下内容的内容，那就太好了：
// 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?
如果可以这样做，编写此类查询的最直接/最有效的方法是什么？
采纳答案by npe
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 ...]
回答by R.Alonso
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.
小心标识字段。
回答by Patrick Ng
If you are using Oracle, you can use rowtype
如果您使用的是 Oracle，则可以使用 rowtype
declare
var_x TABLE_A%ROWTYPE;
Begin
select * into var_x
from TABLE_B where rownum = 1;
update TABLE_A set row = var_x
where ID = var_x.ID;
end;
/
given that TABLE_A and TABLE_B are of same schema
鉴于 TABLE_A 和 TABLE_B 具有相同的架构
回答by Michael
How about using Merge?
使用合并怎么样？
https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
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.
它使您能够运行插入、更新和删除。另一条建议是，如果您要更新带有索引的大型数据集，并且源子集小于目标子集但两个表都非常大，请先将更改移动到临时表。我尝试合并两个表，每个表都有近 200 万行，20 条记录需要 22 分钟。一旦我将增量移动到临时表，就花了几秒钟。
回答by andy wilson
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
您可以通过删除表中的列并重新添加该列并添加您需要的任何默认值来实现。然后保存这将需要重建表
回答by user3170574
Here's a hardcore way to do it with SQL SERVER. Carefully consider security and integrity before you try it, though.
这是使用 SQL SERVER 执行此操作的核心方法。不过，在尝试之前请仔细考虑安全性和完整性。
This uses schema to get the names of all the columns and then puts together a big update statement to update all columns except ID column, which it uses to join the tables.
这使用模式来获取所有列的名称，然后将一个大的更新语句放在一起来更新除 ID 列之外的所有列，它用于连接表。
This only works for a single column key, not composites.
这仅适用于单个列键，不适用于组合。
usage: EXEC UPDATE_ALL 'source_table','destination_table','id_column'
用法：EXEC UPDATE_ALL 'source_table','destination_table','id_column'
CREATE PROCEDURE UPDATE_ALL
@SOURCE VARCHAR(100),
@DEST VARCHAR(100),
@ID VARCHAR(100)
AS
DECLARE @SQL VARCHAR(MAX) =
'UPDATE D SET ' +
-- Google 'for xml path stuff' This gets the rows from query results and
-- turns into comma separated list.
STUFF((SELECT ', D.'+ COLUMN_NAME + ' = S.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @DEST
AND COLUMN_NAME <> @ID
FOR XML PATH('')),1,1,'')
+ ' FROM ' + @SOURCE + ' S JOIN ' + @DEST + ' D ON S.' + @ID + ' = D.' + @ID
--SELECT @SQL
EXEC (@SQL)
回答by Sepehr Kazemi
It is possible. Like npe said it's not a standard practice. But if you really have to:
这是可能的。就像npe所说的那样，这不是标准做法。但如果你真的必须：
1. 首先是一个标量函数CREATE FUNCTION [dte].[getCleanUpdateQuery] (@pTableName varchar(40), @pQueryFirstPart VARCHAR(200) = '', @pQueryLastPart VARCHAR(200) = '', @pIncludeCurVal BIT = 1)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @pQuery VARCHAR(8000);
WITH cte_Temp
AS
(
SELECT
C.name
FROM SYS.COLUMNS AS C
INNER JOIN SYS.TABLES AS T ON T.object_id = C.object_id
WHERE T.name = @pTableName
)
SELECT @pQuery = (
CASE @pIncludeCurVal
WHEN 0 THEN
(
STUFF(
(SELECT ', ' + name + ' = ' + @pQueryFirstPart + @pQueryLastPart FROM cte_Temp FOR XML PATH('')), 1, 2, ''
)
)
ELSE
(
STUFF(
(SELECT ', ' + name + ' = ' + @pQueryFirstPart + name + @pQueryLastPart FROM cte_Temp FOR XML PATH('')), 1, 2, ''
)
) END)
RETURN 'UPDATE ' + @pTableName + ' SET ' + @pQuery
END
DECLARE @pQuery VARCHAR(8000) = dte.getCleanUpdateQuery(<your table name>, <query part before current value>, <query part after current value>, <1 if current value is used. 0 if updating everything to a static value>);
EXEC (@pQuery)
DECLARE @pQuery VARCHAR(8000) = dte.getCleanUpdateQuery('employee', '', 'Unknown', 0);
EXEC (@pQuery)
DECLARE @pQuery VARCHAR(8000) = dte.getCleanUpdateQuery('employee', 'REPLACE(', ', ''#'', '''')', 1);
EXEC (@pQuery)
This query can be improved. This is just the one I saved and sometime I use. You get the idea.
这个查询可以改进。这只是我保存并有时使用的一个。你明白了。
