CODEWITHSUNDEEP
sqlsqlitesql-update
sxingfeng
sxingfeng

Reputation: 1087

How to write SQLite update operation in two tables?

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!

Upvotes: 0

Views: 420

Answers (2)

jjc-Mtl
jjc-Mtl

Reputation: 1

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;

Upvotes: 0

Don Dickinson
Don Dickinson

Reputation: 6258

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;

Upvotes: 1

Related Questions