如何合并Table A与Table B生成新表或更新Table A

各表结构 #

Table A #

Table B #

目标表结构（Table C/更新后Table A） #

前提说明 #

由于两张表未提供关联字段，以下方案默认按行号一一对应进行数据匹配（若存在关联键，替换行号逻辑为关联键即可，结果会更准确）。

一、合并为新表Table C #

使用CREATE TABLE ... SELECT语法创建并填充Table C：

-- 通用SQL（适配PostgreSQL、MySQL 8.0+等）
CREATE TABLE Table_C AS
SELECT 
    a.NAME, 
    a.LASTNAME, 
    a.CITY, 
    b.COUNTRY, 
    b.YEAR
FROM (
    SELECT *, ROW_NUMBER() OVER () AS row_num 
    FROM Table_A
) a
JOIN (
    SELECT *, ROW_NUMBER() OVER () AS row_num 
    FROM Table_B
) b ON a.row_num = b.row_num;

二、更新Table A使其结构与Table C一致 #

分两步操作：先添加缺失列，再同步Table B的数据。

1. 给Table A添加缺失列 #

-- 数据类型请根据实际业务场景调整
ALTER TABLE Table_A
ADD COLUMN COUNTRY VARCHAR(255),
ADD COLUMN YEAR INT;

2. 用Table B的数据更新Table A #

适配PostgreSQL/SQL Server

WITH A_with_row AS (
    SELECT *, ROW_NUMBER() OVER () AS row_num 
    FROM Table_A
),
B_with_row AS (
    SELECT *, ROW_NUMBER() OVER () AS row_num 
    FROM Table_B
)
UPDATE A_with_row
SET COUNTRY = B_with_row.COUNTRY, YEAR = B_with_row.YEAR
FROM A_with_row
JOIN B_with_row ON A_with_row.row_num = B_with_row.row_num;

适配MySQL

UPDATE Table_A a
JOIN (
    SELECT *, ROW_NUMBER() OVER () AS row_num 
    FROM Table_B
) b ON (SELECT ROW_NUMBER() OVER (ORDER BY NAME) FROM Table_A WHERE NAME = a.NAME) = b.row_num
SET a.COUNTRY = b.COUNTRY, a.YEAR = b.YEAR;

注：如果有明确的关联字段（如唯一ID），请将行号关联替换为对应字段关联，避免因行号排序导致的数据匹配错误。

内容的提问来源于stack exchange，提问作者Nelson Romero