用到两个表,teacher和student。teacher的字段和值如下:id(bigint),name(nvarchar),status(smallint)
id name status
1 王老师 0
2 李老师 1
3 余老师 1
4 张老师 2
student的字段和值如下:id(bigint),name(nvarchar),teacherId(bigint)
id name teacherId
1 张三 1
2 李四 1
3 王五 2
4 宋六 1
5 郑七 2
我用这个SQL语句时出现converting data type nvarchar to bigint.怎么解决?delete from student where teacherId in (select stuff(select ','+cast(id as varchar) from teacher where status=1),1,1,''))
id name status
1 王老师 0
2 李老师 1
3 余老师 1
4 张老师 2
student的字段和值如下:id(bigint),name(nvarchar),teacherId(bigint)
id name teacherId
1 张三 1
2 李四 1
3 王五 2
4 宋六 1
5 郑七 2
我用这个SQL语句时出现converting data type nvarchar to bigint.怎么解决?delete from student where teacherId in (select stuff(select ','+cast(id as varchar) from teacher where status=1),1,1,''))
--不过没看明白什么意思,呵呵
delete from student where teacherId in
(select stuff((--少个括号
select ','+cast(id as varchar) from teacher where status=1),1,1,''))
--按你的想法呢,其实你可以这样写,没必要整这么复杂
delete student where
exists (select 1 from teacher where teacher.id=student.teacherId and status=1);
--or
delete student where teacherId
in (select id from teacher where status=1);