当前位置： 文江博客话题详情

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

发布于 2024-08-31 09:14:33 字数 493 浏览 9 评论 0原文

我有两个表：

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

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

我想使用 A 中 A 的所有列的数据更新 B 中具有特定 ID 的记录。此ID同时存在于A和B中。

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

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

原文

I have two tables:

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

A will always be subset of B (meaning all columns of A are also in B).

I want to update a record with a specific ID in B with their data from A for all columns of A. This ID exists both in A and B.

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

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（8）

太傻旳人生 2024-09-07 09:14:33

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

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

You can use the non-standard FROM clause.

UPDATE table_to_be_updated b
SET column1 = a.column1,
    column2 = a.column2,
    column3 = a.column3
FROM lookup_table a
WHERE a.id = b.id
AND b.id = 1
回复 原文
梅窗月明清似水 2024-09-07 09:14:33

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

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

动态 SQL 的一般解决方案

只知道两个表的主键列

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

这是 PostgreSQL 特有的。我正在基于 information_schema 构建动态代码，特别是表 information_schema.columns，即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$;

假设 b 中的列与 a 中的每一列相匹配，但反之则不然。 b 可以有额外的列。

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

db<>fiddle此处
旧sqlfiddle

相关答案及更多解释：

使用普通 SQL 的部分解决方案

了解共享列的列表

您知道两个表共享的列名列表。使用用于更新多列的语法快捷方式 - 无论如何都比迄今为止其他答案建议的要短。

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;

db<>fiddle此处
旧 sqlfiddle

此语法于 2006 年随 Postgres 8.2 引入，早在问题提出之前。 手册中的详细信息。

相关：

已知B中的列列表

如果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;

自然左连接< /a> 连接 b 中的一行，其中所有同名列都保存相同的值。在这种情况下，我们不需要更新（没有任何变化），并且可以在流程的早期消除这些行（WHERE b.id IS NULL）。
我们仍然需要找到匹配的行，因此外部查询中的b.id = ab.id。

db<>fiddle 此处
旧 sqlfiddle

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

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

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

Is there an UPDATE syntax ... without specifying the column names?

General solution with dynamic SQL

Knowing only the primary key column of both tables

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

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 SQL standard and most major RDBMS (except Oracle) have it. But a DO statement with PL/pgSQL code executing dynamic SQL is totally non-standard PostgreSQL syntax.

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 b for every column in a, but not the other way round. b can have additional columns.

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

db<>fiddle here
Old sqlfiddle

Related answers with more explanation:

Partial solutions with plain SQL

Knowing the list of shared columns

You 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;

db<>fiddle here
Old sqlfiddle

This syntax was introduced with Postgres 8.2 in 2006, long before the question was asked. Details in the manual.

Related:

Knowing list of columns in B

If all columns of A are defined NOT NULL (but not necessarily all columns of B),
and you know the column names of B (but not necessarily those of 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 JOIN joins a row from b where 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.id in the outer query.

db<>fiddle here
Old sqlfiddle

This is standard SQL except for the FROM clause.
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 A are defined NOT NULL.

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

回复 原文
唔猫 2024-09-07 09:14:33

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

它适用于 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

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

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

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

终于！它适用于 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)

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

It works on PostgreSQL 9.3.4, but does not work on 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.

It works on DB2 10.5, but does NOT work on 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:

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)
回复 原文
浅笑依然 2024-09-07 09:14:33

这是一个很大的帮助。该代码

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;

运行完美。

请注意，您需要一个括号“”才能

From "tbl_a" a

使其正常工作。

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.

回复 原文
素衣风尘叹 2024-09-07 09:14:33

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

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

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

这避免了更新 B 的需要。

但请务必阅读所有详细信息< /a>.

否则，您所要求的内容不被认为是一个好的实践 - 不鼓励诸如带有 SELECT * ... 的视图之类的动态内容（因为这种轻微的便利可能会破坏更多的东西而不是帮助的东西），并且您的内容request for 相当于 UPDATE ... SET 命令。

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

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.

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 ... SET command.

回复 原文
心如荒岛 2024-09-07 09:14:33

您可以构建并执行动态 sql 来做到这一点，但这确实不理想

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

回复 原文
狂之美人 2024-09-07 09:14:33

不完全是OP的答案，但我在postgresql中找到了一个解决方案来解决我非常相似的问题，供任何搜索者使用。我有一个包含一百多个列的表，我需要用相同的列从另一个表更新一个表。无需太多编码，在两次传递中我避免了大量手动输入。

第一遍使用 string_agg 函数生成更新查询的“设置”部分。

SELECT string_agg('', '"' || column_name::text || '"=' || 's."' || column_name::text || '",')
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = 'source_table'

我运行了这个，然后将单个结果粘贴到更新查询中（删除一个尾随逗号）：

UPDATE target_table 
   SET [pasted_here]
  FROM source_table s
 WHERE target_table.id=s.id

也许这可以巧妙地变成一个步骤，但它已经解决了我的问题。

Not precisely the answer to the OP, but I found a solution in postgresql to my very similar problem, for anyone searching. I had a table with over a hundred columns and I needed to update one table from another with the same columns. Without much coding, in two passes I avoided lots of manual typing.

The first pass used string_agg function to generate my "set" portion of the update query.

SELECT string_agg('', '"' || column_name::text || '"=' || 's."' || column_name::text || '",')
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = 'source_table'

I ran this and then pasted the single result into the update query (removing one trailing comma):

UPDATE target_table 
   SET [pasted_here]
  FROM source_table s
 WHERE target_table.id=s.id

Maybe this could be cleverly made into a single step, but it already solved my problem.

回复 原文
逆流 2024-09-07 09:14:33

尝试以下

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

编辑：- 更新多个列

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

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
回复 原文
~没有更多了~

关于作者

沫离伤花

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

游缘惊梦

文章 0 评论 0

小兔几

文章 0 评论 0

Glik

文章 0 评论 0

生生漫

文章 0 评论 0

Luxian

文章 0 评论 0

Champion-Ming

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文