SQL SQL从另一个表的字段更新一个表的字段

SQL update fields of one table from fields of another one

提问by Nir

I have two tables:

我有两个表：

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

Awill always be subset of B(meaning all columns of Aare also in B).

A将始终是的子集B（意味着 的所有列A也在 中B）。

I want to update a record with a specific IDin Bwith their data from Afor all columns of A. This IDexists both in Aand B.

我想更新与特定的记录ID中B，从他们的数据A进行的所有列A。这ID在A和 中都存在B。

Is there an UPDATEsyntax or any other way to do that without specifying the column names, just saying "set all columns of A"?

是否有UPDATE语法或任何其他方法可以在不指定列名的情况下执行此操作，只需说“设置 A 的所有列”？

I'm using PostgreSQL, so a specific non-standard command is also accepted (however, not preferred).

我正在使用 PostgreSQL，因此也接受特定的非标准命令（但是，不是首选）。

回答by Scott Bailey

You can use the non-standard FROMclause.

您可以使用非标准的FROM子句。

UPDATE b
SET column1 = a.column1,
  column2 = a.column2,
  column3 = a.column3
FROM a
WHERE a.id = b.id
AND b.id = 1

回答by Erwin Brandstetter

_{The question is old but I felt the best answer hadn't been given, yet.}

_{这个问题很老，但我觉得最好的答案还没有给出。}

Is there an UPDATEsyntax ... without specifying the column names?

是否有UPDATE语法...不指定列名？

General solution with dynamic SQL

使用动态 SQL 的通用解决方案

You don't need to know any column names except for some unique column(s) to join on (idin the example). Works reliably for any possible corner case I can think of.

除了要加入的某些唯一列（id在示例中）之外，您不需要知道任何列名称。对于我能想到的任何可能的极端情况，都可以可靠地工作。

This is specific to PostgreSQL. I am building dynamic code based on the the information_schema, in particular the table information_schema.columns, which is defined in the ISO SQL standard and most modern RDBMS (except for Oracle) support it. But a DOstatement with PL/pgSQLcode executing dynamic SQL is totally non-standard PostgreSQL syntax.

这是特定于 PostgreSQL 的。我正在构建基于information_schema 的动态代码，特别是 table information_schema.columns，它在 ISO SQL 标准中定义并且大多数现代 RDBMS（Oracle 除外）都支持它。但是DO带有执行动态 SQL 的PL/pgSQL代码的语句完全是非标准的 PostgreSQL 语法。

DO
$do$
BEGIN

EXECUTE (
SELECT
'UPDATE b
 SET   (' || string_agg(quote_ident(column_name), ',') || ')
     = (' || string_agg('a.' || quote_ident(column_name), ',') || ')
 FROM   a
 WHERE  b.id = 123
 AND    a.id = b.id'
FROM   information_schema.columns
WHERE  table_name   = 'a'       -- table name, case sensitive
AND    table_schema = 'public'  -- schema name, case sensitive
AND    column_name <> 'id'      -- all columns except id
);

END
$do$;

Assuming a matching column in bfor everycolumn in a, but not the other way round. bcan have additional columns.

假设 中b的每一列都有一个匹配的列a，但反过来不行。b可以有额外的列。

WHERE b.id = 123is optional, to update only a selected row.

WHERE b.id = 123是可选的，仅更新选定的行。

SQL Fiddle.

SQL小提琴。

Related answers with more explanation:

有更多解释的相关答案：

• Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql
• Update multiple columns that start with a specific string

• 由于 plpgsql 中字符串周围不需要括号，动态更新失败
• 更新以特定字符串开头的多个列

Partial solutions with plain SQL

使用纯 SQL 的部分解决方案

With list of shared columns

包含共享列列表

You still need to know the list of column names that both tables share. With a syntax shortcut for updating multiple columns - shorter than what other answers suggested so far in any case.

您仍然需要知道两个表共享的列名列表。使用用于更新多列的语法快捷方式 - 比迄今为止在任何情况下建议的其他答案都要短。

UPDATE b
SET   (  column1,   column2,   column3)
    = (a.column1, a.column2, a.column3)
FROM   a
WHERE  b.id = 123    -- optional, to update only selected row
AND    a.id = b.id;

SQL Fiddle.

SQL小提琴。

This syntax was introduced with Postgres 8.2 in Dec. 2006, long before the question was asked.
More details in the manualand this related answer on dba.SE:

这个语法是在 2006 年 12 月在 Postgres 8.2 中引入的，早在这个问题被提出之前。手册中的
更多详细信息以及 dba.SE 上的相关答案：

• Bulk update of all columns

• 批量更新所有列

With list of columns in B

带有列列表 B

Ifall columns of Aare defined NOT NULL(but not necessarily B),
andyou knowthe column names of B(but not necessarily A).

如果 的所有列A都已定义NOT NULL（但不一定B），
并且您知道B（但不一定A）的列名。

UPDATE b
SET   (column1, column2, column3, column4)
    = (COALESCE(ab.column1, b.column1)
     , COALESCE(ab.column2, b.column2)
     , COALESCE(ab.column3, b.column3)
     , COALESCE(ab.column4, b.column4)
      )
FROM (
   SELECT *
   FROM   a
   NATURAL LEFT JOIN  b -- append missing columns
   WHERE  b.id IS NULL  -- only if anything actually changes
   AND    a.id = 123    -- optional, to update only selected row
   ) ab
WHERE b.id = ab.id;

The NATURAL LEFT JOINjoins a row from bwhere all columns of the same name hold same values. We don't need an update in this case (nothing changes) and can eliminate those rows early in the process (WHERE b.id IS NULL).
We still need to find a matching row, so b.id = ab.idin the outer query.

该NATURAL LEFT JOIN联接从行b，其中同名的所有列持有相同的价值观。在这种情况下我们不需要更新（没有任何变化），并且可以在流程早期消除这些行 ( WHERE b.id IS NULL)。
我们仍然需要找到匹配的行，所以b.id = ab.id在外部查询中。

db<>fiddle here
Old sqlfiddle.

db<>fiddle here
旧的sqlfiddle。

This is standard SQL except for the FROMclause.
It works no matter which of the columns are actually present in A, but the query cannot distinguish between actual NULL values and missing columns in A, so it is only reliable if all columns in Aare defined NOT NULL.

这是标准 SQL，除了FROM子句。
无论实际存在于 中的哪些列，它都有效A，但查询无法区分实际的 NULL 值和缺失的列A，因此只有在A定义了所有列时它才是可靠的NOT NULL。

There are multiple possible variations, depending on what you knowabout both tables.

有多种可能的变化，这取决于您对这两个表的了解。

回答by jochan

I have been working with IBM DB2 database for more then decade and now trying to learn PostgreSQL.

我已经使用 IBM DB2 数据库十多年了，现在正在尝试学习 PostgreSQL。

It works on PostgreSQL 9.3.4, but does not work on DB2 10.5:

它适用于 PostgreSQL 9.3.4，但不适用于 DB2 10.5：

UPDATE B SET
     COLUMN1 = A.COLUMN1,
     COLUMN2 = A.COLUMN2,
     COLUMN3 = A.COLUMN3
FROM A
WHERE A.ID = B.ID

Note: Main problem is FROM cause that is not supported in DB2 and also not in ANSI SQL.

注意：主要问题是 DB2 不支持的 FROM 原因，ANSI SQL 也不支持。

It works on DB2 10.5, but does NOT work on PostgreSQL 9.3.4:

它适用于 DB2 10.5，但不适用于 PostgreSQL 9.3.4：

UPDATE B SET
    (COLUMN1, COLUMN2, COLUMN3) =
               (SELECT COLUMN1, COLUMN2, COLUMN3 FROM A WHERE ID = B.ID)

FINALLY! It works on both PostgreSQL 9.3.4 and DB2 10.5:

最后！它适用于 PostgreSQL 9.3.4 和 DB2 10.5：

UPDATE B SET
     COLUMN1 = (SELECT COLUMN1 FROM A WHERE ID = B.ID),
     COLUMN2 = (SELECT COLUMN2 FROM A WHERE ID = B.ID),
     COLUMN3 = (SELECT COLUMN3 FROM A WHERE ID = B.ID)

回答by user2493970

This is a great help. The code

这是一个很大的帮助。编码

UPDATE tbl_b b
SET   (  column1,   column2,   column3)
    = (a.column1, a.column2, a.column3)
FROM   tbl_a a
WHERE  b.id = 1
AND    a.id = b.id;

works perfectly.

完美地工作。

noted that you need a bracket "" in

注意到你需要一个括号“”

From "tbl_a" a

to make it work.

使其工作。

回答by Unreason

Not necessarily what you asked, but maybe using postgres inheritance might help?

不一定是你问的，但也许使用 postgres 继承可能会有所帮助？

CREATE TABLE A (
    ID            int,
    column1       text,
    column2       text,
    column3       text
);

CREATE TABLE B (
    column4       text
) INHERITS (A);

This avoids the need to update B.

这避免了更新 B 的需要。

But be sure to read all the details.

但一定要阅读所有细节。

Otherwise, what you ask for is not considered a good practice - dynamic stuff such as views with SELECT * ...are discouraged (as such slight convenience might break more things than help things), and what you ask for would be equivalent for the UPDATE ... SETcommand.

否则，您要求的内容不被认为是一种好的做法 -SELECT * ...不鼓励使用视图之类的动态内容（因为这种轻微的便利可能会破坏更多的东西而不是帮助的东西），并且您要求的内容与UPDATE ... SET命令等效。

回答by Daniel Brink

you can build and execute dynamic sql to do this, but its really not ideal

您可以构建和执行动态 sql 来执行此操作，但它确实不理想

回答by Salil

Try Following

尝试关注

Update A a, B b, SET a.column1=b.column1 where b.id=1

EDITED:- Update more than one column

编辑：- 更新不止一列

Update A a, B b, SET a.column1=b.column1, a.column2=b.column2 where b.id=1