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: [email protected]
[mailto:[email protected]] On Behalf Of Igor Tandetnik
Sent: Monday, January 25, 2010 5:06 PM
To: [email protected]
Subject: Re: [sqlite] Conditional insertion syntaxe in SQLite
Tiberio, Sylvain <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
