Thanks for the input. The 'Folder' vs 'Folders' problem was merely a spelling mistake here, not in the code.
My problem was that '%' was being escaped in my code. Putting a '%%' fixed the problem. Regards, Jonas On Tue, Mar 11, 2008 at 5:37 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Jonas Sandman wrote: > > > > I must be missing something obvious here... > > > > I have created my database like this: > > > > CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER, > > rootid INTEGER, path VARCHAR(255))"; > > > > The database is filled with files and folders.. > > > > folderid parentid rootid path > > 1 0 1 C:\MP3\Albums\ > > 2 1 1 C:\MP3\Albums\Abba - Definitive Collection\ > > 3 2 1 C:\MP3\Albums\Abba - Definitive Collection\cd1\ > > 4 2 1 C:\MP3\Albums\Abba - Definitive Collection\cd2\ > > > > Then I want to delete 'C:\MP3\Albums' folder and its sub-folders: > > > > I figured this should work: > > DELETE FROM Folder WHERE folderid IN (SELECT folderid FROM Folder > > WHERE path LIKE :PATH || '%'); > > > > Where :PATH is 'C:\MP3\Albums\' > > > > If I run the sub-query by itself it returns the resultset from above > > but when I run it like above, sqlite3_changes(..) returns '1' and only > > the row with folderid=1 is deleted. > > > > What am I doing wrong? > > Well, your problem may be that the table name in the subquery and the > delete statement, "Folder", is different than the name in the create > table statement, "Folders". If I use the correct table name it runs as > expected. > > The more interesting thing I discovered when testing this is that having > the wrong table name in the subquery causes the sqlite3.exe command > shell to terminate. > > The following SQL script terminates when it executes the delete statement. > > > CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER, > rootid INTEGER, path VARCHAR(255)); > > insert into Folders values(1,0,1,'C:\MP3\Albums\'); > insert into Folders values(2,1,1,'C:\MP3\Albums\Abba - Definitive > Collection\'); > insert into Folders values(3,2,1,'C:\MP3\Albums\Abba - Definitive > Collection\cd1\'); > insert into Folders values(4,2,1,'C:\MP3\Albums\Abba - Definitive > Collection\cd2\'); > > SELECT folderid FROM Folders WHERE path LIKE 'C:\MP3\Albums\' || '%'; > > DELETE FROM Folders WHERE folderid IN > (SELECT folderid FROM Folder WHERE path LIKE 'C:\MP3\Albums\' || '%'); > > Dennis Cote > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
