update c set bid = b.id from c inner join a on c.aid = a.id inner join b on a.flag = b.flag
解决方案 »
- sql语句优化,表中有10万条记录
- WIN7(64位)旗舰版安装SQL2005(64位)时提示IIS警告
- SQL,主从表,关联后 有些数据为空,不知道该如何SELECT,有例子表
- 很简单的问题:我把以前装的SQL Server200卸了,再装就装不上了,怎么办啊?!
- 语句和字符串类型的变量如何结合在一起使用??
- 弱弱的提问:用查询分析器登陆时,填的Server是不是就是本地计算机名字
- 求大神鉴定,此人写的这个是脑残误导,还是什么意思
- 有关"select * into backup from source where 1<>1"和自动增值
- asp语法问题
- ADO 连接sql server2005数据库 ,windows AD用户连接,登陆不上?
- 奇怪的问题,一个sql查询的问题,请大侠帮忙看看
- 在存儲過程裏面調用返回值為一個Table的存儲過程,怎么實現。
set bid=b.bid
from c
join a on a.id=c.aid
join b on a.flag=b.flag
set bid=t.id
from c
join a on a.id=c.aid
join (
select min(id) as 'id'
,flag
from b
group by flag
) t on a.flag=t.flag
declare @b table(id int,flag varchar(10))
declare @c table(aid int,bid int)
insert @a
select 1,'one' union
select 2,'two' union
select 3,'three' union
select 4,'four' union
select 5,'five' union
select 6,'six'
insert @b
select 1,'three' union
select 2,'four' union
select 3,'two' union
select 4,'one' union
select 5,'one' union
select 6,'five' union
select 7,'six'
insert @c
select 1,4 union
select 2,3 union
select 3,null union
select 4,5 union
select 5,2 union
select 6,7--更新
update c
set bid=b.id
from @c c
join @a a on a.id=c.aid
join (
select min(id) as 'id'
,flag
from @b
group by flag
) b on a.flag=b.flag--查看
select * from @c--结果
/*
aid bid
----------- -----------
1 4
2 3
3 1
4 2
5 6
6 7(所影响的行数为 6 行)
*/
union select 2,'two'
union select 3,'three'
union select 4,'four'
union select 5,'five'
union select 6,'six'declare @a table(id int,flag varchar(10))insert into @a select 1,'three'
union select 2,'four'
union select 3,'two'
union select 4,'one'
union select 5,'one'
union select 6,'one'
union select 7,'six'declare @c table(aid int,bid int)insert into @c select 1,3
union select 2,4
union select 3,null
union select 4,5
union select 5,2
union select 6,7
update c set bid=isnull(c.bid,1) from @c c,(select a.id aid, b.id bid from @a a ,@b b where a.flag = b.flag ) e where c.aid=e.aidselect * from @c