表table1
字段
id1(int) parentid(int) id2(int)
1 0 1
2 -1 1
3 0 2
4 -1 2
5 0 3
6 -1 3
7 0 4
8 -1 4把parengid字段的值=-1的按照id2相同的那个更新成id1的值
结果如下
id1(int) parentid(int) id2(int)
1 0 1
2 1 1
3 0 2
4 3 2
5 0 3
6 5 3
7 0 4
8 7 4这个更新语句怎么写??谢谢
字段
id1(int) parentid(int) id2(int)
1 0 1
2 -1 1
3 0 2
4 -1 2
5 0 3
6 -1 3
7 0 4
8 -1 4把parengid字段的值=-1的按照id2相同的那个更新成id1的值
结果如下
id1(int) parentid(int) id2(int)
1 0 1
2 1 1
3 0 2
4 3 2
5 0 3
6 5 3
7 0 4
8 7 4这个更新语句怎么写??谢谢
set _a.parentid = _b.id1
from table1 _a
join table2 _b
on _b.id2 = _a.id2
where _a.parentid = -1
and _b.parentid <> -1
不知道对不对,如果不对就先把table2赋到临时表里?
create table table1
(
id1 int ,
parentid int ,
id2 int
)insert into table1 select 1 , 0 , 1
union all select 2 , -1 , 1
union all select 3 , 0 , 2
union all select 4 , -1 , 2
union all select 5 , 0 , 3
union all select 6 , -1 , 3
union all select 7 , 0 , 4
union all select 8 , -1 , 4update a set parentid = b.id1
from table1 a
join (select * from table1 where parentid =0) b on a.id2 =b.id2
where a.parentid =-1 select * from table1
id1 parentid id2
----------- ----------- -----------
1 0 1
2 1 1
3 0 2
4 3 2
5 0 3
6 5 3
7 0 4
8 7 4(所影响的行数为 8 行)