我有一个表格groups
group_id | name_group
1 ISI
2 IZI
还有一张桌子students
id | first_name | last_name | group_id
6 Bob Surname1 1
17 John Surname2 2
如何使用groups.name
从表中删除student
所有信息?
即我需要查询来选择具有相同group_id
的所有学生,这相当于姓名。
group_id 1 = 'ISI'
group_id 2 = 'IZI'
查询必须完全按名称删除。
您可以使用
此查询
Delete from Students where group_id=(Select group_id from groups where name_group='ISI');
这是所有记录与1
group_id
(通过group_name='ISi'
(。
有不同的方法。一个简单的方法可能是选择组的 ID 并从那里删除。例:
DECLARE
@name as nvarchar(20) = 'myName'
-- we display the data just for check
SELECT s.*, g.group_id
FROM students s ON g.group_id = s.group_id
WHERE g.name_group = @name
--we look the group id and delete the matches with students
DELETE
FROM students
WHERE group_id in (SELECT group_id FROM groups WHERE name_group = @name)
PD:这种基本方法可以同时适用于MySQL和MSSQL。