-- 创建目标表
CREATE TABLE IF NOT EXISTS inventory (
product_id INT,
product_name STRING,
stock INT,
last_updated DATE
);
-- 创建源表
CREATE TABLE IF NOT EXISTS daily_stock (
product_id INT,
new_stock INT,
update_date DATE
);
-- 插入初始数据
INSERT INTO inventory VALUES
(1, 'iPhone 15', 100, '2024-06-01'),
(2, 'MacBook Pro', 50, '2024-06-01');
-- 插入每日库存数据
INSERT INTO daily_stock VALUES
(1, 120, '2024-06-02'), -- 更新现有产品
(3, 200, '2024-06-02'); -- 新增产品
基础 Upsert
最常见的场景：根据主键匹配，存在则更新，不存在则插入。
-- 合并每日库存数据到库存表
MERGE INTO inventory AS target
USING daily_stock AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
UPDATE SET
target.stock = source.new_stock,
target.last_updated = source.update_date
WHEN NOT MATCHED THEN
INSERT (product_id, product_name, stock, last_updated)
VALUES (source.product_id, 'New Product', source.new_stock, source.update_date);
结果验证：
SELECT * FROM inventory ORDER BY product_id;
product_id
product_name
stock
last_updated
1
iPhone 15
120
2024-06-02
2
MacBook Pro
50
2024-06-01
3
New Product
200
2024-06-02
⚠️ 注意：
WHEN MATCHED
WHEN MATCHED
子句必须写在
WHEN NOT MATCHED
WHEN NOT MATCHED
之前。
条件更新
仅在满足特定条件时执行更新操作，避免无效写入。
-- 仅当新库存大于旧库存时才更新
MERGE INTO inventory AS target
USING daily_stock AS source
ON target.product_id = source.product_id
WHEN MATCHED AND source.new_stock > target.stock THEN
UPDATE SET
target.stock = source.new_stock,
target.last_updated = source.update_date
WHEN NOT MATCHED THEN
INSERT (product_id, product_name, stock, last_updated)
VALUES (source.product_id, 'New Product', source.new_stock, source.update_date);
结果说明：
product_id = 1
product_id = 1
：新库存 120 > 旧库存 120（上次已更新），不满足条件，不更新。
product_id = 3
product_id = 3
：不匹配，插入新记录。
仅插入新数据
忽略已存在的记录，仅插入源表中新增的数据（类似
INSERT IGNORE
INSERT IGNORE
）。
-- 仅插入不匹配的记录
MERGE INTO inventory AS target
USING daily_stock AS source
ON target.product_id = source.product_id
WHEN NOT MATCHED THEN
INSERT (product_id, product_name, stock, last_updated)
VALUES (source.product_id, 'New Product', source.new_stock, source.update_date);
删除孤立数据
当源数据中标记为删除时，从目标表中移除对应记录。
-- 假设 daily_stock 中 stock = 0 表示下架
MERGE INTO inventory AS target
USING daily_stock AS source
ON target.product_id = source.product_id
WHEN MATCHED AND source.new_stock = 0 THEN DELETE
WHEN NOT MATCHED THEN
INSERT (product_id, product_name, stock, last_updated)
VALUES (source.product_id, 'New Product', source.new_stock, source.update_date);
多条件匹配
使用复杂的
ON
ON
条件进行匹配，适用于复合主键或业务逻辑匹配。
-- 按产品 ID 和日期匹配
MERGE INTO inventory AS target
USING daily_stock AS source
ON target.product_id = source.product_id
AND target.last_updated < source.update_date
WHEN MATCHED THEN
UPDATE SET
target.stock = source.new_stock,
target.last_updated = source.update_date
WHEN NOT MATCHED THEN
INSERT (product_id, product_name, stock, last_updated)
VALUES (source.product_id, 'New Product', source.new_stock, source.update_date);
清理测试数据
完成 Upsert 验证后，建议清理测试表：
-- 删除测试表
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS daily_stock;
