1

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

Amit
  • 5,924
  • 7
  • 46
  • 94
  • 7
    Foreign key, on delete cascade? – jarlh Nov 18 '15 at 20:21
  • @jarlh Sound good. have you try it? Or is just a guess? – Juan Carlos Oropeza Nov 18 '15 at 20:23
  • 1
    @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
  • 3
    Which DBMS are you using? Postgres? Oracle? –  Nov 18 '15 at 20:27
  • @JuanCarlosOropeza, just a guess, hence the question mark. – jarlh Nov 18 '15 at 20:33
  • @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
  • @Phill You mean my fiddle or you create your own? – Juan Carlos Oropeza Nov 18 '15 at 21:28
  • 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

1 Answers1

1

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

Amit
  • 5,924
  • 7
  • 46
  • 94
  • 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