在iPhone中的SQLite3中编写插入查询

Hi need insert data in table if the record is not already exits Ex: IF NOT EXISTS (SELECT Id FROM table WHERE id=_Id) THEN
INSERT INTO tbale(.....)

如果记录尚未退出,您需要在表格中插入数据Ex:IF NOT EXISTS(SELECT Id FROM table WHERE id = _Id)THEN INSERT INTO tbale(.....)

This can be easily done using stored procedure in MySql. But I want to d same thing in SQLite by writing a single query statement.

这可以使用MySql中的存储过程轻松完成。但我希望通过编写单个查询语句在SQLite中做同样的事情。

Pleas help

4 个解决方案

#1

INSERT INTO CategoryMaster (CategoryID, CategoryText) SELECT %d,'%@' WHERE NOT EXISTS (SELECT 1 FROM CategoryMaster WHERE CategoryID= %d)

INSERT INTO CategoryMaster(CategoryID,CategoryText)SELECT%d,'%@'WHERE NOT EXISTS(SELECT 1 FROM CategoryMaster WHERE CategoryID =%d)

Its working for me :)

它为我工作:)

#2

SQlite doesn't have stored procedudes that you need to do logic like this. But you can always extend sqlite with simple C-functions. Or you could simply code this logic in whatever language you are writing your program in. I don't think the performance hit is that great. Did your profiling show that this is a critical path that needs to be optimized?

SQlite没有像这样做逻辑的存储过程。但是你总是可以使用简单的C函数扩展sqlite。或者你可以用你编写程序的任何语言简单地编写这个逻辑。我认为性能不是很好。您的分析是否表明这是一条需要优化的关键路径?

#3

Put a unique index on the id column (or make it the primary key) then use:

在id列上放置一个唯一索引(或使其成为主键),然后使用:

REPLACE INTO table(.....)

see: http://www.sqlite.org/lang_insert.html or http://www.sqlite.org/lang_replace.html

请参阅:http://www.sqlite.org/lang_insert.html或http://www.sqlite.org/lang_replace.html

#4

I would do this by running a first query to see if the record exists then decide whether to run an insert or update. I don't think SQLite has a fully featured query language to support the desired shorter approach.

我会通过运行第一个查询来查看记录是否存在然后决定是否运行插入或更新。我不认为SQLite有一个功能齐全的查询语言来支持所需的更短的方法。

#1