On Fri, 15 Aug 2008, Javier Julio 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?
You can INSERT rows that don't already exist. For example, the following creates 2 tables, FOO and BAR that share a common field (KEY), which is unique in both tables. It then inserts 3 rows into FOO and one row in BAR: create table foo (key integer unique, foodata ); create table bar (key integer unique, bardata ); insert into foo values(1,"one"); insert into foo values(2,"two"); insert into foo values(3,"three"); insert into bar values(2,"two"); To insert into BAR only those 2 rows from FOO that do not exist in BAR: insert into bar (key, bardata) select key, foodata from foo where foo.key not in(select key from bar); BAR now has the same rows as FOO. If you change a data value in row 2 in FOO: update foo set foodata="changed" where key=2; and you want to update BAR with this new value, you can run an update based on a join of the common field 'key': update bar set bardata=(select foodata from foo where key=bar.key); This is a simple example that may not be appropriate for your situation. For instance, the update statement updates ALL rows whether they have different values or not. This may not be suitable for a very large database, but there are other approaches, e.g.: update bar set bardata=(select foodata from foo where key=bar.key and foodata <> bar.bardata); This only updates rows where foodata <> bardata. Depending on the specifics, you may want to index the 'key' fields to optimize the inserts and updates. Also, depending on the nature of the data being update, this may be inefficient. You are probably better off using your code to identify the rows needing updating/inserting then running specific SQL statements. Chris ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Christopher F. Martin School of Medicine Center for Digestive Diseases & Nutrition CB# 7555, 4104 Bioinformatics Bldg. University of North Carolina at Chapel Hill Chapel Hill, North Carolina 27599-7555 Phone: 919.966.9340 Fax: 919.966.7592 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
