php - sql delete with select 条件

Question

也许有人可以帮助我。

我有 2 个 sql 表:

 // groups
 | id_group   | namegroup |
 +------------+-----------+
 | 30         | s         |


 // contacts
 | name | group |
 +------+-------+
 | juan | s     |

我需要从 ID 中删除一个组，但没有与之关联的联系人。我测试了以下查询但不起作用。

DELETE
FROM group
WHERE id_group = 30
AND (
    SELECT
        count(*) AS id
    FROM contacts co
    INNER JOIN GROUP c ON co. GROUP = c.namegroup
    WHERE   c.id_group = 30
) = 0

谢谢

Answer 1

如果您想要删除具有 ID = 30 的组，您可以尝试以下查询，前提是该组未与任何联系人相关联:

查询 #1:

DELETE
`groups`
FROM `groups`
LEFT JOIN contacts
ON contacts.`group` = `groups`.namegroup
WHERE `groups`.id_group = 30
AND contacts.`group` IS NULL;

如果您想删除所有没有任何关联联系人的组，请尝试以下查询:

查询#2:

 DELETE
 `groups`
 FROM `groups`
 LEFT JOIN contacts
 ON contacts.`group` = `groups`.namegroup
 WHERE contacts.`group` IS NULL;