how to delete from one or more tables at the same time using one sql command

Question

I have three tables (ngo, staff and finance). I want to delete all staffs and finance data of a particular ngo if that ngo data is deleted. Now, I am adding data in those tables separately: first, add all ngos; second, add all staffs of the ngo; third, add all finance of the ngo.

Now, let's say I only add ngo and staff and later change my mind to delete the information from the two tables without going further to add finance.

$qry =mysqli_query($mysqli, "
    update ngo, finance, staff 
    set ngo.deletestatus=('delete'), 
        finance.deletestatus=('delete'),
        staff.deletestatus=('delete')
    where ngo.ngo_id=finance.ngo_id
        and ngo.ngo_id=staff.ngo_id 
        and ngo.ngo_id='".$_GET['delet']."'");

This query does not run because finance table is empty. Even if one table is empty, I still want the delete operation to take effect using the same script. Please help!!

first of all ... your script seems to be vulnerable to sql injections. please learn about prepared statements ... — Jakumi, Sep 08 '17 at 13:40
yes. I am using foreign keys. ngo table has link to staff and finance. — moses dwana, Sep 08 '17 at 13:44
this is why we have cascading delete in the database https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete — yacc, Sep 08 '17 at 13:48
i have read it but it is not too clear. sorry i am new to programming and database — moses dwana, Sep 08 '17 at 13:57
The concept is quite easy to understand, see the answers I linked, one of them shows an example. Once you have set it up you just delete one row (the ngo here) and the database takes care of all rows that reference it. — yacc, Sep 08 '17 at 15:56

score 0 · Answer 1 · answered Sep 08 '17 at 16:21

You will need to fix your database design to allow for cascaded deletes/updates. This is how you could do it:

CREATE TABLE ngos (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
);

CREATE TABLE ngostaff (
    ngo_id int unsigned not null,
    staff_id int unsigned not null,
    name VARCHAR(255) default null,
    FOREIGN KEY (ngo_id) REFERENCES ngos (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
);

CREATE TABLE ngofinance (
    ngo_id int unsigned not null,
    finance_id int unsigned not null,
    name VARCHAR(255) default null,
    FOREIGN KEY (ngo_id) REFERENCES ngos (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
);

Deleting a row from the ngo table will automatically delete any rows referencing it.

score 0 · Answer 2 · answered Sep 08 '17 at 16:40

For what it's worth, you're not deleting, you're just setting a deletestatus column to indicate the data is not to be used. Some people call this a "soft delete."

But I understand the issue. You are doing a join between the tables to find matching rows to update, but if one of the tables has no matching data, the join has nothing to match to, and it skips the rows in the first table(s) too.

The easiest solution for your is to do three updates. You should also use a bind parameter to protect against SQL injection.

if ($stmt = mysqli_prepare($mysqli, "
    update ngo set deletestatus = 'delete' where ngo_id=?")) {
    mysqli_stmt_bind_param($stmt, "s", $_GET["delet"]);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}
if ($stmt = mysqli_prepare($mysqli, "
    update finance set deletestatus = 'delete' where ngo_id=?")) {
    mysqli_stmt_bind_param($stmt, "s", $_GET["delet"]);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}
if ($stmt = mysqli_prepare($mysqli, "
    update staff set deletestatus = 'delete' where ngo_id=?")) {
    mysqli_stmt_bind_param($stmt, "s", $_GET["delet"]);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}

This could be a little easier by using a loop:

foreach (["ngo", "finance", "staff"] as $tablename) {
    if ($stmt = mysqli_prepare($mysqli, "
        update `$tablename` set deletestatus = 'delete' where ngo_id=?")) {
        mysqli_stmt_bind_param($stmt, "s", $_GET["delet"]);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_close($stmt);
    }
}

how to delete from one or more tables at the same time using one sql command

2 Answers2