Declare @Table table ( departID int identity(1,1) not null, departName varchar(20) not null ) Declare @tblstudent table ( stuID int identity(1,1) not null, stuName varchar(20) not null, departID int )insert into @table select '部门1' union select '部门2' union select '部门3' union select '部门4'insert into @tblstudent select '学生1',1 union select '学生2',1 union select '学生3',1 union select '学生4',1 select * from @tblstudentbegin tran update @Table set departName='B' where departID in( select departID from @tblstudent b group by b.departID having count(*)>2) if @@error >0 begin rollback tran end else begin commit tran end select * from @Table
这句,是定义了一个列吗,
对于sql来说每条语句都是回滚的,不可能update的时候一半成功一半不成功.所以说这个题目出的很差.
departID int identity(1,1) not null,
departName varchar(20) not null
)
Declare @tblstudent table (
stuID int identity(1,1) not null,
stuName varchar(20) not null,
departID int
)insert into @table select '部门1'
union select '部门2'
union select '部门3'
union select '部门4'insert into @tblstudent select '学生1',1
union select '学生2',1
union select '学生3',1
union select '学生4',1
select * from @tblstudentbegin tran
update @Table set departName='B'
where departID in(
select departID
from @tblstudent b
group by b.departID
having count(*)>2)
if @@error >0
begin
rollback tran
end
else
begin
commit tran
end
select * from @Table