Delete multiple rows while using the id from the row before

debugcn Published at Dev

Thomas Bang Thomas Gaming

I'm trying to create a folder and file system in my database.

And while deleting the folders with the subfolder, it should also delete the corresponding files in the folder from my other table (files_plus). But how can i delete both the folder, it's subfolder and all files contained in the folder and subfolders?

Table: files_plus:

-----------------------------
| id  |  name  |  parentid  |
-----------------------------

(the "parentid" being the "id" from the folders_plus table.)

Table: folders_plus

I can't think of any way to construct a query for doing so, so how would i manage to do this?

Ivar

You can add a foreign key constraint and set it on "CASCADE DELETE" like this for the folders_plus table.

ALTER TABLE `folders_plus` 
ADD INDEX `folders_plus_parentid_idx` (`parentid` ASC);
ALTER TABLE `folders_plus` 
ADD CONSTRAINT `fk_folders_plus_id_folders_plus_parentid`
  FOREIGN KEY (`parentid`)
  REFERENCES `folders_plus` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

And for the files_plus table:

ALTER TABLE `files_plus` 
ADD INDEX `fk_files_plus_id_folders_plus_parentid_idx` (`parentid` ASC);
ALTER TABLE `files_plus` 
ADD CONSTRAINT `fk_files_plus_id_folders_plus_parentid`
  FOREIGN KEY (`parentid`)
  REFERENCES `folders_plus` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

When you delete a parent row, it will also delete all of it's children.

Do make sure that your parentid column is nullable, and that every top level directory is null. Every parentid that is not null, is required to exist as an id in your folders_plus table.

_{PS: Please do note that in order for foreign key constraints to work, your database storage engine should be InnoDB.}

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-30

Comments

0 comments

From Dev

Related Related

Article

Delete multiple rows while using the id from the row before

Delete multiple rows while using the id from the row before

Delete multiple rows by file name using Coldfusion

delete multiple rows in gridview using checkboxes

Delete multiple rows from multiple tables in mysql using single value

Delete multiple rows using IDs?

UPDATE & DELETE multiple rows with an array for the id

Merge multiple rows with same ID into one row

SQLite - How to delete parent row while keeping the child/children rows?

Delete rows from multiple tables as one query

delete row id using Contentprovider

How to delete multiple rows from multiple tables using Where clause?

SQLite select query to get one row from multiple rows have same id

Postgresql delete multiple rows from multiple tables

Trying to delete selected row from datagridview but it is deleting multiple rows

Delete multiple Items from a List of Id's using Entity Framework

Stored procedure: delete rows from multiple tables with output for every row

Delete multiple rows by file name using Coldfusion

How to combine values from multiple rows into a single row using module?

Delete row + the following 2 rows if a certain row name using UNIX

Get row with the lowest id from multiple rows?

How can i delete from the database multiple rows using a checkbox?

Delete a row javascript using row id

Delete multiple rows using IDs?

How do I delete a row when multiple rows have the same id

UPDATE & DELETE multiple rows with an array for the id

Delete row in excel while exporting using php

How do I delete a row from Sqlite using ID where my ID is binary data (GUID)

select single row from multiple rows by id

Delete multiple rows using Jquery

How to delete rows in multiple tables using pdo