匯入資料為何蓋掉本地職員備註？

先核來源與已訂規則

本例同編號已存在，來源只可改 display；本地 Keep 備註須留，即使來源附 null。真正新編號可用所附初始備註。更正 UPSERT 的衝突分支只改 display，新建分支另沿初始規則。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"id":1,"display":"New","note":null}

下載可編輯 CSV ↓

比較兩條實際路徑

錯衝突更新把備註成 null；更正編號 1 是 New、Keep。准改一個來源欄，不等於整行都交來源覆寫。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	[{"id":1,"display":"New","note":null}]
更正路徑	[{"id":1,"display":"New","note":"Keep"}]

下載可編輯 CSV ↓

本地 Node.js 程序：22.23.3 或 24.14.1

import {DatabaseSync} from 'node:sqlite';
import {TextDecoder} from 'node:util';
import {inflateSync} from 'node:zlib';
import {posix} from 'node:path';
import {createHash} from 'node:crypto';
const rows=(db,sql)=>db.prepare(sql).all().map(r=>({...r}));
const failed=(action)=>{try{action();return null;}catch(e){return {code:e.errcode??e.code??e.name};}};

import assert from 'node:assert/strict';
const run=function run(i,corrected=true){
    const db=new DatabaseSync(':memory:');try{db.exec("CREATE TABLE target(id INTEGER PRIMARY KEY,display TEXT,note TEXT);INSERT INTO target VALUES(1,'Old','Keep');");
      db.prepare('INSERT INTO target VALUES(?,?,?) ON CONFLICT(id) DO UPDATE SET display=excluded.display'+(corrected?'':',note=excluded.note')).run(i.id,i.display,i.note);
      return rows(db,'SELECT * FROM target ORDER BY id');
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"id":1,"display":"New","note":null},"expected":[{"id":1,"display":"New","note":null}],"corrected":false},{"label":"corrected base","input":{"id":1,"display":"New","note":null},"expected":[{"id":1,"display":"New","note":"Keep"}],"corrected":true},{"label":"new record has supplied initial note","input":{"id":2,"display":"New","note":"Initial"},"expected":[{"id":1,"display":"Old","note":"Keep"},{"id":2,"display":"New","note":"Initial"}],"corrected":true},{"label":"permitted empty display value","input":{"id":1,"display":"","note":null},"expected":[{"id":1,"display":"","note":"Keep"}],"corrected":true}];
for(const test of cases){
  const actual=run(test.input,test.corrected);
  assert.deepEqual(actual,test.expected,test.label);
  console.log(test.label,actual);
}

下載可執行例子 ↓

下次載入前核邊界

新編號 2 可用 Initial；舊 1 的 display 若依約容許空，Keep 仍須留。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
新行的初始備註	{"id":2,"display":"New","note":"Initial"}	[{"id":1,"display":"Old","note":"Keep"},{"id":2,"display":"New","note":"Initial"}]
舊行顯示欄容許空值	{"id":1,"display":"","note":null}	[{"id":1,"display":"","note":"Keep"}]

下載可編輯 CSV ↓

來源 null 應否清掉舊編號 1 的 Keep？

查看參考答案

不應，舊行備註由目標持有，只改 display，Keep 照留。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源