Deleting from multiple tables in mysql
I'd like to combine the following two queries. Records will always exist in the users table and the active_kpi table, but they may not exist in the recipients table. Even if records are not in the recipients table, I still want the query to delete records from the other two tables.
$sql = "DELETE u, ak FROM users u JOIN active_kpi ak ON ak.group_id =
u.group_id WHERE u.group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
$sql = "DELETE FROM recipients WHERE group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
php mysql sql-delete
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
add a comment |
I'd like to combine the following two queries. Records will always exist in the users table and the active_kpi table, but they may not exist in the recipients table. Even if records are not in the recipients table, I still want the query to delete records from the other two tables.
$sql = "DELETE u, ak FROM users u JOIN active_kpi ak ON ak.group_id =
u.group_id WHERE u.group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
$sql = "DELETE FROM recipients WHERE group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
php mysql sql-delete
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
add a comment |
I'd like to combine the following two queries. Records will always exist in the users table and the active_kpi table, but they may not exist in the recipients table. Even if records are not in the recipients table, I still want the query to delete records from the other two tables.
$sql = "DELETE u, ak FROM users u JOIN active_kpi ak ON ak.group_id =
u.group_id WHERE u.group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
$sql = "DELETE FROM recipients WHERE group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
php mysql sql-delete
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
I'd like to combine the following two queries. Records will always exist in the users table and the active_kpi table, but they may not exist in the recipients table. Even if records are not in the recipients table, I still want the query to delete records from the other two tables.
$sql = "DELETE u, ak FROM users u JOIN active_kpi ak ON ak.group_id =
u.group_id WHERE u.group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
$sql = "DELETE FROM recipients WHERE group_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$group_id]);
php mysql sql-delete
php mysql sql-delete
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
edited Nov 23 '18 at 22:50
Funk Forty Niner
80.6k1247102
80.6k1247102
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
asked Nov 23 '18 at 22:40
Thomas GrauerThomas Grauer
1008
1008
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
try this:
DELETE u,ak,r
FROM users u INNER JOIN active_kpi ak ON ak.group_id = u.group_id
LEFT JOIN recipients r ON ak.group_id = r.group_id
WHERE u.group_id = ?
note that left join is used to covering rows with lack of recipients.
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53453589%2fdeleting-from-multiple-tables-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
try this:
DELETE u,ak,r
FROM users u INNER JOIN active_kpi ak ON ak.group_id = u.group_id
LEFT JOIN recipients r ON ak.group_id = r.group_id
WHERE u.group_id = ?
note that left join is used to covering rows with lack of recipients.
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
add a comment |
try this:
DELETE u,ak,r
FROM users u INNER JOIN active_kpi ak ON ak.group_id = u.group_id
LEFT JOIN recipients r ON ak.group_id = r.group_id
WHERE u.group_id = ?
note that left join is used to covering rows with lack of recipients.
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
add a comment |
try this:
DELETE u,ak,r
FROM users u INNER JOIN active_kpi ak ON ak.group_id = u.group_id
LEFT JOIN recipients r ON ak.group_id = r.group_id
WHERE u.group_id = ?
note that left join is used to covering rows with lack of recipients.
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
try this:
DELETE u,ak,r
FROM users u INNER JOIN active_kpi ak ON ak.group_id = u.group_id
LEFT JOIN recipients r ON ak.group_id = r.group_id
WHERE u.group_id = ?
note that left join is used to covering rows with lack of recipients.
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
edited Nov 24 '18 at 7:16
Valerian Pereira
462311
462311
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
answered Nov 23 '18 at 22:46
FatemehNBFatemehNB
25126
25126
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53453589%2fdeleting-from-multiple-tables-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
