> > Javier Julio <[EMAIL PROTECTED]> wrote: >> Is it possible in SQLite to have a single statement that basically >> says if this row exists run an UPDATE statement, if not run an >> INSERT? > > In some situations, depending on your definition of "exists", INSERT > OR > REPLACE statement may be suitable: > > http://sqlite.org/lang_insert.html > http://sqlite.org/lang_conflict.html > > Otherwise you will have to emulate it in your program code. Perform an > UPDATE, use sqlite3_changes to see if any rows were actually > modified as > a result, if not run INSERT. > >> I believe these are done in procedures which I know is not supported >> in SQLite. > > But I'm pretty sure the language you write your host application in > supports some concept of a subroutine. > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Igor, I have seen that you help out others often and am grateful you find the time. I've looked into the INSERT OR REPLACE docs. It should have made sense to me before but was probably overwhelmed with it at first. I've dug through it again and am wondering then maybe if this will do it. I will have an id field that will always be unique. It won't be an auto increment field since I'll be using the id from the resultset coming from the server so I will manually insert that id. It's an auto incremented on the server's database so its always unique. So from the description: "When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs." Reading that carefully I figure that means if I have a record in the ideas table with an ideaId of 5 and I perform an INSERT with that same ideaId of 5 (remember I always provide the id as I want to use the id from the server) then it removes that row, and replaces it with the newly provided one? It seems I need to make sure I set a UNIQUE constraint on that ideaId field in my ideas table and not just set it as a PRIMARY KEY. Does the INSERT OR REPLACE Internally figure out whether to do an INSERT or UPDATE? Maybe it does a delete and then an insert every time? If that is correct then yes that is all I need. My only concern is maybe I don't want to do this for each row of data if its not necessary. For example, maybe that record has had no change based on a date field. Do you know if that INSERT OR REPLACE is an expensive operation? I probably should be cautious and not do that unless necessary. Since I'll be syncing the data one way, I probably should run a simple SELECT statement first to check the last modified date. Thanks Igor. Ciao! Javi _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
