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()
**/
;

-- 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()
**/
;

-- 3. upsert - 累加 c_count (已知新版server on conflict 表达式读取已有值问题)
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
/**
onerror(continue)
**/
;

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

-- 4. upsert - 保留某些字段不更新 (已知新版server on conflict 表达式读取已有值问题)
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
/**
onerror(continue)
**/
;

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