Sqlite:如果不存在则插入，如果存在则更新 [英] Sqlite: Insert if not exist, Update if exist

问题描述

我有一个包含 2 个表的数据库，如下所示:

cg_resp身份证 |姓名 |电子邮件1 |乔治 |乔治@yahoo.com

id 列是 primary_key,autoincremented 和 name 是 unique

和

equip_info身份证 |说明 |cg_resp_id1 |卡尺 |1

在申请表中，我有 2 个名为 edit_resp_name 和 edit_resp_email

的编辑框

如果用户在电子邮件 [e-mail] 中插入一个新的负责人姓名，例如 John，那么在保存表单期间我想insertcode> 一个新的负责人进入cg_resp 表，获取最后插入的id 并将其更新为equip_info.cg_resp_id.

如果用户保持名称 George 但它正在更新电子邮件，如 [e-mail] 那么我想 update updatecode>id = 1 来自 cg_resp 与新的电子邮件地址和其余的(equip_info.cg_resp_id 和 cg_resp.id) 保持不变.

如果负责人的名称相同，我想维护表equip_info中cg_resp_id的原始引用，因此有必要避免删除和插入等情况新的.

如何在一个 Sqlite sql 序列中完成此操作?

解决方案

使用 INSERT OR REPLACE这正是您想要的:在名称不存在时插入新行或以其他方式更新.

I have a database with 2 tables like this:

cg_resp
id  | name   | email
1   | George | [e-mail]

id column is primary_key,autoincremented and name is unique

and

equip_info
id  | description  | cg_resp_id 
1   | Caliper      | 1

In the application form I have 2 edit boxes named edit_resp_name and edit_resp_email

If user insert a new responsible name like John with the email [e-mail] then during the save of form I would like to insert a new responsible into cg_resp table, get the last inserted id and update it to equip_info.cg_resp_id.

If the user maintain the name George but it's updating the email like [e-mail] then I would like to update the id = 1 from cg_resp with the new email address and the rest of them (equip_info.cg_resp_id and cg_resp.id) to remain the same.

I would like to maintain the original reference of cg_resp_id from table equip_info if the name of responsible is the same, so it's necessary to avoid situation like delete and insert a new one.

How can be done this in one Sqlite sql sequence?

解决方案

Use INSERT OR REPLACE which does exactly what you want : insert a new row when the name does not exists or update otherwise.

这篇关于Sqlite:如果不存在则插入，如果存在则更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！