SQL筆記:新增(Insert)或更新(Update)一次完成

在寫資料庫的時候往往會遇到不存在就新增(INSERT)若存在就更新(UPDATE)的議題,有人偏好兩段式或分流處理,但其實可以把這個東西整併起來變成單一處理工序,寫起來程式碼會更精簡一點,端視使用情境而定嘍。

Upsert:若資料存在就更新,不然就新增資料

要注意的是若在很嚴謹的環境下,資料庫新增工序還是要鎖定資料列(ROWLOCK)並包入交易(Transaction)中,這無關分離執行或合併執行的問題(都會發生),不然在多重執行緒的WEB環境極有可能產生兩筆重複的PK,萬一資料庫沒有設定唯一鍵或複合索引鍵而出現這種問題就得不償失了。

以下是主要SQL寫法:

IF EXISTS
  (
    SELECT
      1
    FROM
      [cTableMain] WITH (ROWLOCK)
    WHERE
      cUserID = @cUserID
  )
  BEGIN
    UPDATE
	    [cTableMain]
    SET
	    cUserName = @cUserName,
	    iUserMoney = @iUserMoney
    OUTPUT
	    INSERTED.iAutoIndex
    WHERE
	    cUserID = @cUserID
  END
ELSE
  BEGIN
    INSERT INTO
	    [cTableMain]
    OUTPUT
      INSERTED.iAutoIndex
    VALUES
    (
      @cUserID,
      @cUserName,
      @iUserMoney
    )
  END

以下是程式碼示範(使用了ADO.NET與Dapper寫法):

try
{ //主要SQL指令
  string cSQL = $@"請自行將上面的SQL語法轉換至此";
  //連線資料庫並設定交易
  using var oConn = new System.Data.SqlClient.SqlConnection(cYourDatabasePath);
  oConn.Open();
  using var oTrans = oConn.BeginTransaction(System.Data.IsolationLevel.Serializable);
  try
  {
    var iAutoIndex = oConn.QuerySingle<int>(
      cSQL,
      new {
        cUserID = oData.cUserID,
        cUserName = oData.cUserName,
        iUserMoney = oData.iUserMoney
      },
      oTrans
    );
    oTrans.Commit();
    //如果成功就將iAutoIndex回寫到資料物件包
    oData.iAutoIndex = iAutoIndex;
  }
  catch (System.Exception oEx)
  {
    oTrans.Rollback();
    throw new System.Exception($"新增或更新失敗:{oEx.Message}。");
  }
  //回傳資料
  //return序列化成功oData物件資料
}
catch (System.Exception oEx)
{
  //return序列化失敗oData物件資料
}

假設上面是一隻WebAPI,則當前端回傳一個資料物件包(oData)到後端時,它會使用cUserID去搜尋是否存在該筆資料(假設cUserID是唯一鍵值),若存在就進行ROWLOCK鎖定並開始既有資料的欄位更新作業。若不存在該筆cUserID資料就進行插入資料列動作。最後所有的動作完成後,統一傳回該資料表的自動增量索引鍵(iAutoIndex)供給後續使用。

SQL Insert Update Upsert Dapper