基于Lookup Table填充数据库表字段的技术咨询

嘿，这个需求其实挺常见的，我来给你捋几种实用的方案，适配不同场景和主流数据库（MySQL、SQL Server、PostgreSQL都覆盖到）~

假设你的目标表是TABLE B，它包含对应TABLE A代码的Column C，以及需要填充消息的Column D，下面分场景说明：

1. 一次性批量更新现有数据 #

如果TABLE B已经有一批数据，需要把Column D批量填充为TABLE A对应的消息，用JOIN关联更新最直接：

MySQL 写法 #

UPDATE TABLE B
JOIN TABLE A ON TABLE B.`Column C` = TABLE A.`Column A`
SET TABLE B.`Column D` = TABLE A.`Column B`;

SQL Server 写法 #

UPDATE B
SET B.`Column D` = A.`Column B`
FROM TABLE B B
INNER JOIN TABLE A A ON B.`Column C` = A.`Column A`;

PostgreSQL 写法 #

UPDATE TABLE B
SET "Column D" = A."Column B"
FROM TABLE A A
WHERE TABLE B."Column C" = A."Column A";

提示：如果TABLE B里存在TABLE A没有的代码，用LEFT JOIN替代INNER JOIN的话，对应Column D会被设为NULL，你可以根据需求加COALESCE设置默认值，比如COALESCE(A.Column B, '未知消息')。

2. 插入新数据时自动填充消息 #

如果每次往TABLE B插数据时，都想直接从TABLE A拉取对应消息，可以在插入语句里关联查找表：

INSERT INTO TABLE B ("Column C", "Column D", other_column1, other_column2)
SELECT 
    source.code_col, 
    A."Column B", 
    source.other_val1, 
    source.other_val2
FROM your_source_table source
LEFT JOIN TABLE A A ON source.code_col = A."Column A";

3. 自动同步查找表更新（长期维护方案） #

因为你的TABLE A偶尔会手动新增值，要是想让TABLE B的消息自动同步更新，可以用以下两种方案：

方案A：触发器 #

当TABLE B插入/更新数据时，自动触发查询TABLE A填充消息，以MySQL为例：

DELIMITER //
CREATE TRIGGER fill_msg_after_insert
AFTER INSERT ON TABLE B
FOR EACH ROW
BEGIN
    UPDATE TABLE B
    SET "Column D" = (SELECT "Column B" FROM TABLE A WHERE "Column A" = NEW."Column C")
    WHERE id = NEW.id; -- 假设TABLE B有主键`id`
END //

CREATE TRIGGER fill_msg_after_update
AFTER UPDATE OF "Column C" ON TABLE B
FOR EACH ROW
BEGIN
    UPDATE TABLE B
    SET "Column D" = (SELECT "Column B" FROM TABLE A WHERE "Column A" = NEW."Column C")
    WHERE id = NEW.id;
END //
DELIMITER ;

方案B：生成列（计算列） #

如果你的数据库支持，用生成列可以让Column D自动关联TABLE A的值，无需手动维护，比如：

SQL Server（持久化计算列）

ALTER TABLE TABLE B
ADD "Column D" AS (
    SELECT TOP 1 "Column B" FROM TABLE A WHERE TABLE A."Column A" = TABLE B."Column C"
) PERSISTED;

PostgreSQL（存储生成列）

ALTER TABLE TABLE B
ADD COLUMN "Column D" TEXT GENERATED ALWAYS AS (
    (SELECT "Column B" FROM TABLE A WHERE TABLE A."Column A" = TABLE B."Column C")
) STORED;

注意：生成列依赖TABLE A的数据一致性，确保TABLE A的Column A是唯一约束，否则会因为返回多行报错。

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