odbtools/odbctest/mql/aaa/17conflict/11upsert.mql

-- ============================================================
-- Upsert 操作测试
-- ============================================================

-- 1. 基本 upsert - 插入新数据（name不存在）
insert into /test/aaa/test_conflict (c_name, c_value, c_count) values ('conflict_upsert_001', 500, 1) on conflict update c_value=c_value
;

select * from /test/aaa/test_conflict where c_name='conflict_upsert_001'
/**
output()
count(1)
**/
;

-- 2. upsert - 更新已存在数据
insert into /test/aaa/test_conflict (c_name, c_value, c_count) values ('conflict_upsert_001', 999, 5) on conflict update c_value=c_value, c_count=c_count
;

select c_value, c_count from /test/aaa/test_conflict where c_name='conflict_upsert_001'
/**
output()
match("c_value","999")
match("c_count","5")
**/
;

-- 3. upsert - 累加 c_count
insert into /test/aaa/test_conflict (c_name, c_value, c_count) values ('conflict_002', 200, 5) on conflict update c_count=c_count+c_count
;

select c_count from /test/aaa/test_conflict where c_name='conflict_002'
/**
output()
match("c_count","6")
**/
;

-- 4. upsert - 保留某些字段不更新
insert into /test/aaa/test_conflict (c_name, c_value, c_count) values ('conflict_001', 888, 10) on conflict update c_count=c_count+c_count
;

select c_value, c_count from /test/aaa/test_conflict where c_name='conflict_001'
/**
output()
match("c_value","100")
match("c_count","11")
**/
;