MySQL关联表操作：如何将inventory_2表inventory数据插入inventory_1表

你当前的需求是更新inventory_1表中已存在的product_id对应的inventory字段值，而非新增记录，因为inventory_1已经提前存在所有匹配product_id的行数据。

通用关联更新写法（适配PostgreSQL、SQL Server等数据库） #

UPDATE inventory_1
SET inventory = inventory_2.inventory
FROM inventory_2
WHERE inventory_1.product_id = inventory_2.product_id;

MySQL 专属更新写法 #

UPDATE inventory_1 i1
INNER JOIN inventory_2 i2 ON i1.product_id = i2.product_id
SET i1.inventory = i2.inventory;

若需支持“不存在则插入、存在则更新”的Upsert场景 #

如果inventory_1可能不存在对应product_id的记录，需要兼顾新增和更新逻辑，可以用对应数据库的Upsert语法：

• MySQL写法：

INSERT INTO inventory_1 (product_id, inventory)
SELECT product_id, inventory FROM inventory_2
ON DUPLICATE KEY UPDATE inventory = VALUES(inventory);

• PostgreSQL写法：

INSERT INTO inventory_1 (product_id, inventory)
SELECT product_id, inventory FROM inventory_2
ON CONFLICT (product_id) DO UPDATE SET inventory = EXCLUDED.inventory;

结果校验 #

执行完更新操作后，可以执行以下语句校验数据是否符合预期：

SELECT 
  i1.product_id, 
  i1.inventory AS current_inventory, 
  i2.inventory AS target_inventory
FROM inventory_1 i1
JOIN inventory_2 i2 ON i1.product_id = i2.product_id;

返回结果中current_inventory和target_inventory的值应该完全一致。

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