首页 > sql >  在SQLite中插入或更新行

在SQLite中插入或更新行 (Insert or update a row in SQLite)

2013-04-20 sqlsqliteupsert

问题

我找到了这个答案,但我不确定它是否符合我的要求。看起来它更新了表中的所有行,我只想更新一行。

我的表非常简单:

MacAddress           |    Name
1C-6F-65-C6-20-0B    |    Logan-PC
DE-AD-BE-EF-F0-0D    |    PC1
...

MacAddress是PK,我想简单地能够更新名称,如果它存在,或者如果不存在则添加新行,给定MacAddressas引用和Name字符串。

这是我第一次使用SQLite,所以我可能无法正确理解Eric B的答案。

解决方法

假设您的MacAddress是表中的主键,您发布的答案似乎对我有用。

insert or replace into yourtable (macaddress, name)
values ('1C-6F-65-C6-20-0B', 'Logan-PC');

insert or replace into yourtable (macaddress, name)
values ('1C-6F-65-C6-20-0B', 'Logan-PC-Updated');

SQL小提琴演示

这将导致单行,Logan-PC-Updated作为更新名称。

注意:如果您的MacAddress不是您的主键,则此方法将不起作用。

问题

I found this answer but I'm not sure it does what I want to. It looks like it updates ALL rows in the table, where I only want to update one.

My table is very simple:

MacAddress           |    Name
1C-6F-65-C6-20-0B    |    Logan-PC
DE-AD-BE-EF-F0-0D    |    PC1
...

MacAddress is the PK and I want to simply be able to update Name if it exists or add a new row if it does not, given the MacAddress as references and a Name string.

This is the first time I'm using SQLite so I may just not be understanding Eric B's answer correctly.

解决方法

The answer you posted seems to work well for me assuming your MacAddress is a primary key in your table.

insert or replace into yourtable (macaddress, name)
values ('1C-6F-65-C6-20-0B', 'Logan-PC');

insert or replace into yourtable (macaddress, name)
values ('1C-6F-65-C6-20-0B', 'Logan-PC-Updated');

SQL Fiddle Demo

This will result in a single row, with Logan-PC-Updated as the updated name.

NOTE: If your MacAddress is not your primary key, then this method will not work.

相似信息