SQL 資料存在就更新 / 不存在就新增 (sql 存在 update 不存在 insert)

SQL 資料存在就更新 / 不存在就新增 (sql 存在 update 不存在 insert)

SQL 資料存在就更新 / 不存在就新增 (sql 存在 update 不存在 insert)


資料來源: https://blog.csdn.net/diaofeiyang/article/details/122255803

https://zhuanlan.zhihu.com/p/101613827
https://write.corbpie.com/how-to-do-insert-with-an-update-on-duplicate-using-sqlite/


方案01.[先刪除 後新增]

REPLACE INTO basic_params (param_key ,param_value,updated_time) VALUES ('012','0101','2022-06-19 11:11:00');


方案02.(MYSQL)

INSERT INTO basic_params (param_key ,param_value,created_time,updated_time) VALUES("01","0101","2022-06-19 11:12:00","2022-06-19 11:12:00") 
ON DUPLICATE KEY UPDATE param_value=VALUES(param_value),updated_time=VALUES(updated_time)

INSERT INTO basic_params (param_key ,param_value,created_time,updated_time)  VALUES("01","0101","2022-06-19 11:12:00","2022-06-19 12:12:00") 
ON DUPLICATE KEY UPDATE param_value=VALUES(param_value),updated_time=VALUES(updated_time)


方案03.(SQLite)

INSERT INTO basic_params (param_key ,param_value,created_time,updated_time) VALUES('012','0101','2022-06-19 11:11:00','2022-06-19 11:11:00') 
ON CONFLICT(param_key) DO UPDATE SET updated_time = '2022-06-19 11:11:00';

INSERT INTO basic_params (param_key ,param_value,created_time,updated_time) VALUES('012','0101','2022-06-19 11:11:00','2022-06-19 11:11:00') 
ON CONFLICT(param_key) DO UPDATE SET updated_time = '2022-06-19 12:11:00';

CREATE TABLE basic_params
(
   param_key            varchar(60) not null,
   param_value          text not null,
   created_time         timestamp,
   updated_time         timestamp,
   primary key (param_key)
)

2 thoughts on “SQL 資料存在就更新 / 不存在就新增 (sql 存在 update 不存在 insert)

  1. C# + SQLite 範例 [新增時4個欄位;修改時2個欄位]


    String SQL = String.Format("INSERT INTO printer_config (printer_sid ,param_value,created_time,updated_time) VALUES('{0}','{1}','{2}','{2}') ON CONFLICT(printer_sid) DO UPDATE SET param_value='{1}',updated_time = '{2}'"
    , SQLDataTableModel.m_printer_valueList[m_intIndex].SID
    , JsonClassConvert.printer_config2String(SQLDataTableModel.m_printer_valueList[m_intIndex].printer_config_value)
    , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
    SQLDataTableModel.SQLiteInsertUpdateDelete(SQL);

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *