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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
