新手简单SQL语句编写。

Question

用户表user,字段有：id,name...
角色表role,字段有：id,name...
关联表userrole，字段有:userId,roleId
问：把角色名称为“研发人员”的用户“张三”从用户表中删除。
哪里错了呀？
DELETE FROM USER u
WHERE u.id=
(
SELECT u.id FROM USER u
JOIN userrole ur ON u.id=ur.userId
JOIN role r ON ur.RoleId=r.id
WHERE u.name='张三' AND r.name='研发人员'
);

Answer 1

delete 不允许子查询有对同表的操作（具体怎么说忘了，大体那么个意思）！！！楼主可百度下：

 DELETE FROM USER u
WHERE u.id=
(
 SELECT t.id FROM
(
SELECT u.id FROM USER u
JOIN userrole ur ON u.id=ur.userId
JOIN role r ON ur.RoleId=r.id
WHERE u.name='张三' AND r.name='研发人员'
)t
);