如何实现update select 语句

前言：

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

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

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

文章目标：

实现update select 的几种常见办法
1. join
2. merge
3. 子查问
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 aaSET new_field = bb.new_fieldFROM     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 oldsusing newdb news on olds.new_field = news.new_fieldwhen matchedupdate set olds.new_field = news.new_field

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

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

MERGE INTO wines USING (VALUES('Chateau Lafite 2003', '24')) vON v.column1 = w.winenameWHEN 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的实现理论状况复杂多变，这里只列举了最简略的应用状况。