当删除另一个表中的特定行时，从表中删除行

发布于 2024-09-27 18:24:24 字数 495 浏览 13 评论 0原文

我有一个组，每个组都有与其关联的联系人。当用户删除群组时，如果该群组不为空，则会提醒用户，如果继续删除该群组中的所有联系人，都将被删除。无论如何，所以我的问题是设置该功能。

我试图弄清楚如何删除属于该组的所有联系人并删除该组。

在继续之前，我想知道是否有一种通过外键自动执行此操作的方法？

好吧，如果不是也没关系，这是我的查询，但 SQL Workbench 抛出以下错误

DELETE c
FROM `list_`.`contacts` AS c
INNER JOIN `list_`.`groups` AS g ON c.group_id = g.id
WHERE g.group = 'School' 
  AND c.user_id = 2;

：

Error Code: 1046 No database selected

这里真的很困惑，我也尝试过 c.*

原文

I have Group and each group has contacts associated with it. When a user deletes a group, if the group is not empty then it will alert them that all contacts in that group will be deleted if they continue. Well anyways, so my problem is setting up that feature.

I have tried to figure out how I can delete all contacts that belong to that group and delete the group as well.

Before I continue I'm wondering but is there a sorta of automated way of doing this via foreign keys?

Well if not its ok, this is my query but SQL Workbench is throwing out the following error

DELETE c
FROM `list_`.`contacts` AS c
INNER JOIN `list_`.`groups` AS g ON c.group_id = g.id
WHERE g.group = 'School' 
  AND c.user_id = 2;

error:

Error Code: 1046 No database selected

really confused here, also I have also tried c.*

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一身骄傲 2024-10-04 18:24:24

MySQL 支持在单个语句中删除多表 - 使用：

DELETE c, g
  FROM `list_`.`contacts` AS c
  JOIN `list_`.`groups` AS g ON c.group_id = g.id
                            AND g.group = 'School' 
 WHERE c.user_id = 2;

关于错误代码 1046，使用 WorkBench 时，请确保在“对象浏览器”选项卡上方的下拉菜单中选择了适当的数据库/目录。您可以指定连接的默认架构/数据库/目录 - 单击 Workbench 初始屏幕的 SQL Development 标题下的“管理连接”选项。

MySQL supports multi-table deletions in a single statement - use:

DELETE c, g
  FROM `list_`.`contacts` AS c
  JOIN `list_`.`groups` AS g ON c.group_id = g.id
                            AND g.group = 'School' 
 WHERE c.user_id = 2;

Regarding error code 1046, when using WorkBench make sure the appropriate database/catalog is selected in the drop down menu found above the Object Browser tab. You can specify the default schema/database/catalog for the connection - click the "Manage Connections" options under the SQL Development heading of the Workbench splash screen.

回复收藏 0 原文

幽梦紫曦～ 2024-10-04 18:24:24

您没有说明您的 RDBMS，但在 SQL Server 中您可以打开级联删除，但是我不建议这样做；太危险了。

更新：MySQL InnoDB也支持级联删除：外键约束

首先手动删除所有引用行，然后删除该组会更安全。

错误消息“错误代码：1046 未选择数据库”表明这不是您的 TSQL 的错误。您指向数据库吗？

回复收藏 0 原文

缺⑴份安定 2024-10-04 18:24:24

在Mysql中，你可以尝试这样的触发器：

DELIMITER $
DROP TRIGGER IF EXISTS `deluser`$

CREATE TRIGGER `deluser` BEFORE DELETE on `biguser`
FOR EACH ROW
BEGIN
DELETE FROM smalluser WHERE id=OLD.id;
END$

DELIMITER ;

注意：触发器必须在删除之前，否则你可能会丢失用于删除记录的密钥。

In Mysql you can try something like this for the trigger:

DELIMITER $
DROP TRIGGER IF EXISTS `deluser`$

CREATE TRIGGER `deluser` BEFORE DELETE on `biguser`
FOR EACH ROW
BEGIN
DELETE FROM smalluser WHERE id=OLD.id;
END$

DELIMITER ;

Note: the trigger has to be before delete otherwise you might lose the key you want to use to delete the records.

回复收藏 0 原文