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)”
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);
SQL 預防 重複 新增