如何实现 update select 语句

前言：

​ 有些时候咱们会遇到如下状况，咱们须要依赖一张表的查问后果来更新另一张表，比方咱们存在一张主表和一张关联表，咱们须要把关联表的局部字段数据同步到主表的外面。

​ 这次的文章呈现也是因为这样一个相似的需要，集体须要把一个 30 万行（后续会发文介绍常见的解决伎俩）的数据文件入库，同时须要将局部字段迁徙到另一张表，两个表之间通过 两个字段 进行 and 匹配。上面画一下结构图：

​

​ 解决形式也比较简单，间接应用 sql 就能够实现，这篇文章针对这个小需要，总结一下 update select 的几种实现形式。

文章目标：

1. 实现 update select 的几种常见办法

   1. join
   2. merge
   3. 子查问
2. merge 的踩坑和问题

筹备数据

​ 为了更好的进行实际操作，这里构建两张简略的表来模仿场景。间接复制上面的 db 即可，因为不同数据库 sql 不同，这里应用的是postgreSql 数据库。

旧表

CREATE TABLE "public"."olddb" (
  "id" int4 NOT NULL,
  "relevance1" varchar(255) COLLATE "pg_catalog"."default",
  "relevance2" varchar(255) COLLATE "pg_catalog"."default",
  "new_field" varchar(255) COLLATE "pg_catalog"."default",
  CONSTRAINT "olddb_pkey" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."olddb" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."olddb"."id" IS '主键';

COMMENT ON COLUMN "public"."olddb"."relevance1" IS '关联字段 1';

COMMENT ON COLUMN "public"."olddb"."relevance2" IS '关联字段 2';

COMMENT ON COLUMN "public"."olddb"."new_field" IS '新字段，须要由关联表同步';

表胜利创立之后，在外部退出一些简略的数据：

新表

CREATE TABLE "public"."newdb" (
  "id" int4 NOT NULL,
  "relevance1" varchar(255) COLLATE "pg_catalog"."default",
  "relevance2" varchar(255) COLLATE "pg_catalog"."default",
  "new_field" varchar(255) COLLATE "pg_catalog"."default",
  CONSTRAINT "newdb_pkey" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."newdb" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."newdb"."id" IS '主键';

COMMENT ON COLUMN "public"."newdb"."relevance1" IS '关联字段 1';

COMMENT ON COLUMN "public"."newdb"."relevance2" IS '关联字段 2';

COMMENT ON COLUMN "public"."newdb"."new_field" IS '新字段，须要同步到旧表';

揭示：留神数据库是postgresql，其余数据库可能存在字段等差异而无奈胜利

表胜利创立之后，在外部退出一些简略的数据：

实现形式汇总

join

​ 第一种的连贯形式应用的是连贯表的 join 办法，咱们通过关联字段查出对应的关联记录，同时在关联之后将关联新字段的数据更新到旧表，这样就实现了每关联一条记录就更新一条记录数据：

​ 实现形式也比较简单，只须要应用

UPDATE olddb aa
SET new_field = bb.new_field
FROM
     newdb bb where aa.relevance1 = bb.relevance1 
    AND aa.relevance2 = bb.relevance2

他的执行后果如下：

上面的下方是错的，这时候 sql 会抛出一个谬误。

UPDATE olddb ALIAS 
SET (new_field) = (
    SELECT
        (bb.new_field) 
    FROM
        olddb aa
        JOIN newdb bb ON aa.relevance2 = bb.relevance2 
        AND aa.relevance1 = bb.relevance1 
    )

Merge（未验证）

第二种形式可能比拟生疏，因为 merge 算是对于 insert 以及 update 的一个统合，粗略理解了一下发现无能不少事件，上面说下。

留神上面的办法在 postgresql 报错 ，起因是是我的 postgresql 版本太低，然而集体在降级过后还不能反对应用 merge 办法，所以这里保留了 sql，能够改变后尝试到其它的数据库语言进行应用。

ERROR: syntax error at or near “MERGE”

很头疼，在 stackflow 也没用找到答案。

merge into olddb as olds
using newdb news on olds.new_field = news.new_field
when matched
update set 
olds.new_field = news.new_field

须要留神的是不同的数据库对于 merge 的个性是不统一的，倡议查看以后装置数据库的版本以及文档进行确认比拟稳当。

上面是 postgresql 的 merge 应用案例，留神个别倡议版本为 11 以上再应用merge。

MERGE INTO wines 
USING (VALUES('Chateau Lafite 2003', '24')) v
ON v.column1 = w.winename
WHEN NOT MATCHED 
  INSERT VALUES(v.column1, v.column2)
WHEN MATCHED
  UPDATE SET stock = stock + v.column2;

子查问

​ 子查问是最简略也是最容易想到的一种形式，不过子查问有一个显著的毛病就是数据量较大的状况下通常性能都比拟差，这种操作通常适宜数据量比拟小的状况，上面是对应的案例语法：

UPDATE olddb 
SET new_field = (SELECT newdb.new_field FROM newdb WHERE olddb.relevance1 = newdb.relevance1 AND olddb.relevance2 = newdb.relevance2)

上面是子查问须要留神的点：

• 如果子查问无奈找到任何匹配的行，则更新后的值将被更改为 NULL
• 如果子查问找到多个匹配的行，update 查问将返回一个谬误。

谬误的信息如下：

> ERROR: more than one row returned by a subquery used as an expression

(\> 谬误: 作为表达式应用的子查问返回多行)

• 少数状况下子查问的性能较差，尽量避免应用

总结：

​ 因为 merge 集体应用经验不足，花了较多工夫仍然没有解决，所以文章题目进行了标记，后续应用了其余的形式避开问题。

​ update select 的实现理论状况复杂多变，这里只列举了最简略的应用状况。