SQL - Recursive delete in one query?

Question

Assuming I have a DB like this:

Folders (with "parent folder" column)
Files (with "folder" column)

Is there a way to delete all files in a folder that has sub folders in only one query?

Example:

Folders:
- id,name,parent
- 1, folder1, 0
- 2, folder2, 1
- 3, folder3, 2
Files:
- name, folder
- file1, 2

And I try to delete folder1. That single query should delete all files in folder2, and folder3 becasue folder2 is under folder1, and folder2 is under folder1.

** I know I can do this as a recursive script, but I want to educate myself more

@JuanCarlosOropeza I had done similar, multiple times in the past. Depending on your needs, a cascade delete is perfect for this. — Phill, Nov 18 '15 at 20:24
@Phill looks like doesnt work in SQL Sever 2014 http://sqlfiddle.com/#!6/e6d84/1 Says: `may cause cycles or multiple cascade paths.` — Juan Carlos Oropeza, Nov 18 '15 at 20:45
@Amit do you mean recursive query? because that is the way you can solve this question. — Juan Carlos Oropeza, Nov 18 '15 at 20:48
The "delete cascade" sounds realy good for this, Im reading about it and I think that can work. My curiosity has reached satisfaction :) Thanks for all of you helpers! — Amit, Nov 18 '15 at 21:01
@Amit I already try on SQL Server 2014 and doesnt seem to work. What is your db version? Also you mean recursive script or recursive query? — Juan Carlos Oropeza, Nov 18 '15 at 21:03
@JuanCarlosOropeza I ment I know I can just write a PHP code that does that, but I want it to be pure SQL. This is an academic question, and not an acctual one that I encountered now or in the past. I usually run XAMPP for my enviorment. (Mysql). — Amit, Nov 18 '15 at 21:09
@amit check this example http://stackoverflow.com/a/21378603/3470178 — Juan Carlos Oropeza, Nov 18 '15 at 21:23
@JuanCarlosOropeza i donno what sqlfiddle is but it works on my local version of sql server 2014. — Phill, Nov 18 '15 at 21:26
I'm running sql server on my computer, it works fine. I don't know what sql fiddle is, never seen it before. — Phill, Nov 18 '15 at 21:52
@Phil sqlfiddle is a site where you can create a test database, from mysql to postgres or oracle. You can see the schema i try here http://sqlfiddle.com/#!6/e6d84/1 — Juan Carlos Oropeza, Nov 19 '15 at 14:14
SQLFIDDLE is not working for me. "ERR_CONNECTION_TIMED_OUT". The solution of delete cascade is good, but is there a way not only to delete folder3, but also to delete the files in it? that is my main problem here. — Amit, Nov 19 '15 at 18:01

score 1 · Accepted Answer · answered Jan 31 '17 at 17:40

1

As suggested by @jarlh, a really nice solution is having a Foreign key, with on delete cascade.

answered Jan 31 '17 at 17:40

Amit

5,118
6
39
82

This totally works, IFF you don't need to do any of your own bookkeeping DURING the cascaded delete. Since it now becomes a "db-managed recursive action", the only way to affect it's behavior is usually something like triggers, which a lot of times is less than an optimal solution. But it you don't have this requirement, this answer works, and it works quite well. – SlimsGhost Jan 31 '17 at 19:14

SQL - Recursive delete in one query?

1 Answers1