這樣的sql語句怎麼寫？
有兩個表a,b，根據a表中的groupid   state=1   任意選n條記錄插入倒b表中，然後更新table   a   這n條記錄的狀態為0。
table   a
id     name         groupid     state
1       model1           1               1      
2       model2           1               1
3       model3           1               1
4       model4           1               0
5       model5           2               1
6       model6           2               0
7       model7           2               0
8       model8           2               1
9       model9           2               1

table   b
bid   id

bid   為自增列



------解决方案--------------------
"groupid state=1 " groupid state=1都要为1么 ？
------解决方案--------------------
可以先更新，后插入

先在a上创建update触发器
create trigger up_a
for update
as
if update(state)
begin
insert b(id)
select id from deleted
end
go


随机更新3条记录：
update #temp
set state=0
where id in(select top 3 id from #temp where state=1 order by newid())

------解决方案--------------------
Insert b Select Top 3 id From A Where groupid = 1 And state = 1 Order By NewID()

Update A Set state = 1
From A
Inner Join B
On A.id = B.id

Or

Insert b Select Top 3 id From A Where state = 1 Order By NewID()

Update A Set state = 1
From A
Inner Join B
On A.id = B.id
------解决方案--------------------
create table a(id int,name varchar(20) ,groupid int ,state int )
create table b(bid int identity(1,1),id int )

drop table a

可以用个触发器
create trigger tr_1 on a
for update
as
if (select groupid from inserted) =1 and (select state from inserted) =1
insert into b select id from inserted
update a set state=0 where id=(select id from inserted)


go

insert into a
select 1, 'model1 ',1,0

select * from a
select * from b

update a set state=1 where id=1


不知道理解有没有错误

------解决方案--------------------
rookie_one(流氓会武术，谁都挡不住
你好象没重新更新a state=0
我学的不好 写的比较冗杂
create trigger tr_1 on a
for update
as
if (select state from inserted) =1
insert into b select id from inserted
update a set state=0 where id=(select id from inserted)
go

------解决方案--------------------
跟上面的差不多
create trigger tr_b on a
for update
as
if update(state)
begin