Is it possible to delete from three tables in a single delete query?

Question

I've been stuck in this code where I should delete data from 3 tables using the client_accounts, with the same id of that specific user. this code doesn't delete and I need all your help, thank you.

<?php
include "connect.php";

$index = $_POST['index'];

$msg = [];

$sql = "DELETE client_accounts, ct_trans, ct_update
        FROM client_accounts
        LEFT JOIN ct_trans ON client_accounts.ct_id=client_accounts.client_id
        LEFT JOIN ct_update ON client_accounts.ct_id=ct_update.ct_id
        WHERE client_accounts.client_id = '$index';";
if($db->query($sql)){
    $msg['status'] = true;
    $msg['message'] = "Deleted Successfully";
}else {
    $msg['status'] = false;
    $msg['message'] = "Deletion Failed";
}
echo json_encode($msg);

?>

this is the jquery part

function deleteclient(i){
$.post("delclient.php",{"index":i},function(response){
    var data = JSON.parse(response);
    $("#message").text(data.message);

    getclient();
})
}

Does this answer your question? [How to delete from multiple tables in MySQL?](https://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) — kmoser, Apr 23 '20 at 06:53
Please post the exact SQL statement being generated, not the PHP code which creates the SQL statement. — kmoser, Apr 23 '20 at 06:54

score 2 · Accepted Answer · answered Apr 23 '20 at 02:55

2

Your first join condition looks wrong, and you probably intended this:

DELETE client_accounts, ct_trans, ct_update
FROM client_accounts
LEFT JOIN ct_trans ON client_accounts.ct_id = ct_trans.client_id
LEFT JOIN ct_update ON client_accounts.ct_id = ct_update.ct_id
WHERE client_accounts.client_id = ?;

That being said, a possible better design decision here would have been to use cascading deletion in the ct_trans and ct_update tables:

ALTER TABLE ct_trans ADD FOREIGN KEY (client_id) REFERENCES client_accounts (ct_id)
    ON DELETE CASCADE;
ALTER TABLE ct_update ADD FOREIGN KEY (ct_id) REFERENCES client_accounts (ct_id)
    ON DELETE CASCADE;

With these two foreign key costraints in place, deleting a parent record in the client_accounts table will trigger a cascading deletion which will also remove all child records in the ct_trans and ct_update tables, which are linked to that parent record. Now, all you would need is:

DELETE FROM client_accounts WHERE client_id = ?;

answered Apr 23 '20 at 02:55

Tim Biegeleisen

502,043
27
286
360

ALTER TABLE ct_trans ADD FOREIGN KEY (client_id) REFERENCES client_accounts (ct_id) ON DELETE CASCADE; ALTER TABLE ct_update ADD FOREIGN KEY (ct_id) REFERENCES client_accounts (ct_id) ON DELETE CASCADE; where will i put this two sir? – secret Apr 23 '20 at 03:28
Can't create table `jaasm`.`ct_trans` (errno: 150 "Foreign key constraint is incorrectly formed" – secret Apr 23 '20 at 03:50
I can't help you, because I don't know what you are doing. If you have some other problem, I suggest opening a new question. No one can sit with you and debug via comments. – Tim Biegeleisen Apr 23 '20 at 03:53
i figured it out, thank you very much for the help, thank you thank you, i appreciate it a lot – secret Apr 24 '20 at 04:33

Is it possible to delete from three tables in a single delete query?

1 Answers1