how to write sqlite update operation in two tables?

Question

I am using SQLite,

TABLE A
(
 ID
 FileNAME
 FOLDERID
)

TABLE B
(
 FOLDERID
 FOLDERPATH
)

I want to write a sql statement to delete all files in A where its Folder is subfolder of C:\ABC\; How Can I make it in one sqlite statement, and is it the best way to do?

Many thanks!

Answer 1

the following works fine:

create table table_a (

   id int,
   file_name varchar(300),
   folder_id int
);

create table table_b (
 folder_id int,
 folder_path varchar(300)
);

insert into table_a (id, file_name, folder_id) values (1, 'file1.txt', 1);
insert into table_a (id, file_name, folder_id) values (1, 'file2.txt', 1);
insert into table_a (id, file_name, folder_id) values (1, 'file2-1.txt', 2);
insert into table_b (folder_id, folder_path) values (1, 'c:\abc\somefolder\another');
insert into table_b (folder_id, folder_path) values (2, 'c:\abcNOT\somefolder\another');

delete
from table_a
where folder_id in (select folder_id from table_b where substr(folder_path, 1, 7) = 'c:\abc\');

select * from table_a;

Answer 2

Same idea but with some modifications: SQLite recognize referential integrity now:

CREATE TABLE Table_a ( id int, file_name varchar(300), folder_id int, FOREIGN KEY(folder_id) REFERENCES Table_b (folder_id) );

CREATE TABLE Table_b ( folder_id int, folder_path varchar(300) );

CREATE TABLE Table_b (folder_id, folder_path) VALUES (1, 'c:\abc\somefolder\another'); CREATE TABLE Table_b (folder_id, folder_path) VALUES (2, 'c:\abcNOT\somefolder\another'); CREATE TABLE Table_a (id, file_name, folder_id) VALUES (1, 'file1.txt', 1); CREATE TABLE Table_a (id, file_name, folder_id) VALUES (1, 'file2.txt', 1); CREATE TABLE Table_a (id, file_name, folder_id) VALUES (1, 'file2-1.txt', 2);

DELETE FROM Table_a WHERE folder_id IN (SELECT folder_id FROM Table_b WHERE folder_path LIKE 'c:\abc\%');

SELECT * FROM Table_a;