Thank you Igor for you quick answer! I would like to implement the range deletion algorithm in SQL to avoid to maintain several implementation (C++, PHP...). If there is no other solution I will do it in C++ and in PHP!
Your SQL statements doesn't work in my case, because I have a trigger that ignore the insertion of existing range. For instance: INSERT INTO range VALUES(1,100,199) Do nothing if the entry (1,100,400) exist because [100,199] is already set to level=1 by [100,400]. So I must delete the range [100,400] before create [100,199] and [201,400]... Is it possible to write something like that in SQL (for SQLite of course!): If [toDeleteMin,toDeleteMax] is include in an existing range Store the existing range value into min and max variables Delete [Min,Max] Create [Min,toDeleteMin-1] Create [toDeleteMax+1,max] endif Regards, Sylvain -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Monday, January 25, 2010 5:06 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Conditional insertion syntaxe in SQLite Tiberio, Sylvain <sylvain.tibe...@eads.com> wrote: > So I would like to write something like that: > CASE WHEN > EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range > WHERE 200 BETWEEN roomIdMin AND roomIdMax > AND 400 BETWEEN roomIdMin AND roomIdMax) THEN DELETE > FROM range WHERE roomidm...@min AND roomidm...@max; INSERT INTO range > VALUES (1, @min, 199 ); INSERT INTO range VALUES (1, 301 , @max ); > END Things like this are best done in your application code, rather than a trigger. SQLite in particular has a limited trigger language, nowhere near Turing-complete (the way Transact-SQL or PL/SQL are). However, if you absolutely insist, you can do something like this: insert into range select 1, roomIdMin, 199 from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax; insert into range select 1, 301, roomIdMax from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax; delete from range where rowid = (select rowid from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax); Note that if the condition doesn't hold (there is no row encompassing both 200 and 400), all three statements are simply no-ops. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users