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

Question

我正在尝试找到一种更新记录的方法，而不必列出需要更新的每个列名。

例如，如果我可以使用类似以下的内容就好了：

// 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'

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

- BinaryCat

---

你如何知道要为每个列分配什么值？ - Michael G

没有实际的方法，你可以生成代码或执行删除-插入对。在99%的情况下，两者都不切实际。 - usr

你能删除现有的行条目，然后进行插入吗？这样做依赖于序列列位置，并且通常不是最佳实践。 - Michael G

@MichaelG - 但是，为了使用INSERT，您仍然需要列出每个列，对吗？还是有一种方法只通过传递唯一记录ID来插入记录？ - BinaryCat

@BinaryCat，你可以不列出列名进行插入。VALUES()的顺序必须反映您正在更新的表的序号列位置。无论哪种方式，您都需要至少列出一次列名。 - Michael G

Answer 1

这是不可能的。

你试图做的事情不属于SQL规范，也不被任何数据库供应商支持。请查看MySQL, Postgresql, MSSQL, Oracle, Firebird, Teradata的SQL UPDATE语句规范。它们每一个都仅支持以下语法：

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

- npe

---

是的，我在发布问题之前花了数小时研究解决方案，以为可能有一些只有专家才知道的高级方法可以完成它。 - BinaryCat

我使用NimbleText.com来协助完成这个任务。我并不是作者，只是这个工具能够快速、轻松地编辑一百行的设置语句。 - undefined

Answer 2

这是不可能的，但是...

你可以做到：

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

注意身份字段的使用。

- R.Alonso

Answer 3

以下是使用SQL SERVER进行硬核操作的方法。在尝试之前，请仔细考虑安全性和完整性。

此方法使用 schema 获取所有列的名称，然后组合一个大型 update 语句来更新除 ID 列之外的所有列，它使用 ID 列来连接表格。

此方法仅适用于单列键，而不适用于复合键。

用法：执行 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)

- user3170574

Answer 4

在Oracle PL/SQL中，您可以使用以下语法：

DECLARE
  r my_table%ROWTYPE;
BEGIN
  r.a := 1;
  r.b := 2;
  ...

  UPDATE my_table
  SET ROW = r
  WHERE id = r.id;
END;

当然，这只是将负担从“UPDATE”语句转移到记录构造上，但您可能已经从某个地方获取了该记录。

- Lukas Eder

Answer 5

如果你正在使用 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;
/

假设TABLE_A和TABLE_B具有相同的模式

- Patrick Ng

Answer 6

如何使用 Merge？

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

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

- Michael

---

MERGE让你可以一次性完成更新、插入和删除，但是对于每个条件，你仍然需要像它们是分开的一样编写所有的SQL。 - E-Rock

Answer 7

这是可能的。就像npe所说的那样，这不是一种标准做法。但如果你真的必须这样做：

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)

这个查询可以进行优化。这只是我保存的一个示例，有时我会使用它。你明白我的意思。

- Sepehr Kazemi

Answer 8

类似于upsert，您可以检查表中是否存在该项，如果存在，则删除它并使用新值插入（技术上更新），但如果行ID对您很重要，则会丢失。

请注意，这就是updelsert。

IF NOT EXISTS (SELECT * FROM Employee WHERE ID = @SomeID)

    INSERT INTO Employee VALUES(@SomeID, @Your, @Vals, @Here)

ELSE

    DELETE FROM Employee WHERE ID = @SomeID
    INSERT INTO Employee VALUES(@SomeID, @Your, @Vals, @Here)

- iMagic

Answer 9

您可以通过在表中删除该列，然后重新添加该列并添加所需的默认值来完成此操作。然后保存更改需要重建表。

- andy wilson