表tb--bh(编号),dj(单价),bz(备注)--以前的编号是1,单价随便吧是5,备注是空的 --现在的编号是2,单价是空的,备注是1--把第一条的单价更新到第二条,对应关系是第一条的bh=第二条的bz请问怎么批量更新?????表结构如下:
create table tb(bh int,dj int,bz int)insert into tb(bh,dj) values(1,5)
insert into tb(bh,bz) values(2,1)
insert into tb(bh,dj) values(3,2)
insert into tb(bh,bz) values(3,3)select * from tb
from tb t
--> Title : Generating test data [t1]
--> Author : 各位大大,本大俠只想要顆星星
--> Date : 2009-11-30 13:48:31
if object_id('[tb]') is not null drop table [tb]
go
create table tb(bh int,dj int,bz int)insert into tb(bh,dj) values(1,5)
insert into tb(bh,bz) values(2,1)
insert into tb(bh,dj) values(3,2)
insert into tb(bh,bz) values(3,3)
update t set dj=isnull((select min(dj) from tb where bh=t.bz),dj)
from tb t select * from tb
/*
警告: 彙總或其他 SET 作業已刪除 Null 值。
bh dj bz
----------- ----------- -----------
1 5 NULL
2 5 1
3 2 NULL
3 2 3
*/
set a.dj=b.dj
from tb a,tb b
where a.bz=b.bhselect * from tb/**
bh dj bz
----------- ----------- -----------
1 5 NULL
2 5 1
3 2 NULL
3 2 3(所影响的行数为 4 行)
**/
-- Author: T.O.P
-- Create date: 2009/11/30
-- Version: SQL SERVER 2005
-- =============================================
create table tb(bh int,dj int,bz int)insert into tb(bh,dj) values(1,5)
insert into tb(bh,bz) values(2,1)
insert into tb(bh,dj) values(3,2)
insert into tb(bh,bz) values(3,3);
with cte as
(
select *, ID=ROW_NUMBER() OVER(ORDER BY GETDATE()) from tb
)
update a
set a.dj = b.dj
from cte a cross apply ( select dj from cte where bh = a.bz and id = a.id-1 ) b
where a.dj is nullselect * from tb
drop table tb
--测试结果:
/*
bh dj bz
----------- ----------- -----------
1 5 NULL
2 5 1
3 2 NULL
3 2 3
*/
insert into tb(bh,bz) values(2,1)
insert into tb(bh,dj) values(3,2)
insert into tb(bh,bz) values(3,3)update
t
set
dj=isnull((select min(dj) from tb where bh=t.bz),dj)
from
tb t
select * from tb
/*bh dj bz
----------- ----------- -----------
1 5 NULL
2 5 1
3 2 NULL
3 2 3
*/