小桀
用一张表中的数据去更新另一张表

用一张表中的数据去更新另一张表

需求产生于我们有两张表，一张表A中的数据为量一张表B中数据的复制品，结果B表结构增加了一些字段，A表未增加这些字段，最后导致接口数据不一致，目标是为A表添加这些字段，并用B表的数据更新A表以后数据。我开发的SQL如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28



ALTER TABLE "mall"."t_mat_mall_favorites_info" 
    ADD COLUMN applicable_product_line VARCHAR ( 256 ) COLLATE "pg_catalog"."default",
    ADD COLUMN category VARCHAR ( 32 ) COLLATE "pg_catalog"."default",
    ADD COLUMN design_content VARCHAR ( 256 ) COLLATE "pg_catalog"."default",
    ADD COLUMN materials_technology VARCHAR ( 256 ) COLLATE "pg_catalog"."default",
    ADD COLUMN surface_technics VARCHAR ( 256 ) COLLATE "pg_catalog"."default";

COMMENT ON COLUMN "mall"."t_mat_mall_favorites_info"."applicable_product_line" IS '适用产品线';
COMMENT ON COLUMN "mall"."t_mat_mall_favorites_info"."category" IS '类目';
COMMENT ON COLUMN "mall"."t_mat_mall_favorites_info"."design_content" IS '图案内容';
COMMENT ON COLUMN "mall"."t_mat_mall_favorites_info"."materials_technology" IS '材料工艺';
COMMENT ON COLUMN "mall"."t_mat_mall_favorites_info"."surface_technics" IS '表面工艺';

UPDATE t_mat_mall_favorites_info 
SET 
    applicable_product_line = t_mat_mall_material.applicable_product_line,
    category = t_mat_mall_material.category,
    design_content = t_mat_mall_material.design_content,
    materials_technology = t_mat_mall_material.materials_technology,
    surface_technics = t_mat_mall_material.surface_technics,
    gmt_modify_time = now() 
FROM
    t_mat_mall_material 
WHERE
    t_mat_mall_favorites_info.ID = t_mat_mall_material.ID

20210508后续：

这件事情有后续的，上面开发的SQL无法在DMS中使用，我开发了下面的SQL：


UPDATE t_mat_mall_favorites_info 
SET applicable_product_line = ( SELECT applicable_product_line FROM t_mat_mall_material WHERE t_mat_mall_favorites_info.ID = t_mat_mall_material.ID ),
category = ( SELECT category FROM t_mat_mall_material WHERE t_mat_mall_favorites_info.ID = t_mat_mall_material.ID ),
design_content = ( SELECT design_content FROM t_mat_mall_material WHERE t_mat_mall_favorites_info.ID = t_mat_mall_material.ID ),
materials_technology = ( SELECT materials_technology FROM t_mat_mall_material WHERE t_mat_mall_favorites_info.ID = t_mat_mall_material.ID ),
surface_technics = ( SELECT surface_technics FROM t_mat_mall_material WHERE t_mat_mall_favorites_info.ID = t_mat_mall_material.ID )

但是非常奇怪的是，之前版本开发的SQL只修改了