求一高效率的更新

[jyh070207]

由于需要更新的数据量很大,需要有效率
根据示例数据要求,根据#t2表的code及dt检查,
如果在#t1表没有对应的code及dt,则将#t1表的amount改为0,
如有对应的code及dt而且#t2.amount > #t1.amount 则将#t1.amount 改为#t2.amount,
小于的不改
注意,在#t1同一code及dt可能有多条amount不同的记录存在,
#t2中,code+dt唯一
示例数据如下:
create table #t1(id int identity,code varchar(10),dt datetime,amount int)
insert into #t1(code,dt,amount)
select 'a01','2011-09-15',20
union all
select 'a01','2011-09-15',10
union all
select 'a01','2011-09-12',2
union all
select 'a01','2011-09-14',15
union all
select 'a01','2011-09-14',30
union all
select 'a02','2011-09-12',2
union all
select 'a02','2011-09-14',15
union all
select 'a02','2011-09-14',30

create table #t2(id int identity,code varchar(10),dt datetime,amount int)
insert into #t2(code,dt,amount)
select 'a01','2011-09-15',15
union all
select 'a01','2011-09-16',2
union all
select 'a01','2011-09-14',18
union
select 'a02','2011-09-14',20


更新后#t1的资料应为
select code,dt,amount from #t1
code dt amount
a01 2011-09-15 15--此处将20改为15,因在#t2中,code=a01 and dt = 2011-09-15的amount = 15
a01 2011-09-15 10--这个不改,因10<15
a01 2011-09-12 0--此处将2改为0,因在#t2中,没有对应的code 及dt
a01 2011-09-14 15--这个不改
a01 2011-09-14 18--此处将30改为18
a02 2011-09-12 0--此处将2改为0,因在#t2中,没有对应的code 及dt
a02 2011-09-14 15--这个不改
a02 2011-09-14 20--此处将30改为20

[geniuswjt]

瞟了一眼应该是case when，等会仔细看下

[NBDBA]

说反了吧

如有对应的code及dt而且#t2.amount > #t1.amount 则将#t1.amount 改为#t2.amount,
小于的不改

a01 2011-09-15 15--此处将20改为15,因在#t2中,code=a01 and dt = 2011-09-15的amount = 15
a01 2011-09-15 10--这个不改,因10<15

这两段反了，不知哪个为准，不过没关系，反了你就自己改回来

UPDATE T1 SET
   amount  = CASE WHEN T2.CODE IS NULL THEN 0
             WHEN t2.amount < t1.amount THEN t2.amount
             ELSE T1.amount
             END
FROM #T1 T1 LEFT JOIN #T2 T2
ON T1.CODE = T2.CODE
AND T1.DT = T2.DT

[jyh070207]

[Quote=引用 2 楼 nbdba 的回复:]
说反了吧

如有对应的code及dt而且#t2.amount > #t1.amount 则将#t1.amount 改为#t2.amount,
小于的不改

a01 2011-09-15 15--此处将20改为15,因在#t2中,code=a01 and dt = 2011-09-15的amount = 15
a01 2011-09-15 10--这个不改,因10<15

这两段反了……
[/Quote]

是说反了,
如有对应的code及dt而且#t2.amount <= #t1.amount 则将#t1.amount 改为#t2.amount,
大于的不改
注意,在#t1同一code及dt可能有多条amount不同的记录存在,

[-晴天]

create table #t1(id int identity,code varchar(10),dt datetime,amount int)
insert into #t1(code,dt,amount)
select 'a01','2011-09-15',20 union all
select 'a01','2011-09-15',10 union all
select 'a01','2011-09-12',2 union all
select 'a01','2011-09-14',15 union all
select 'a01','2011-09-14',30 union all
select 'a02','2011-09-12',2 union all
select 'a02','2011-09-14',15 union all
select 'a02','2011-09-14',30
create table #t2(id int identity,code varchar(10),dt datetime,amount int)
insert into #t2(code,dt,amount)
select 'a01','2011-09-15',15 union all
select 'a01','2011-09-16',2 union all
select 'a01','2011-09-14',18 union all
select 'a02','2011-09-14',20
go
update t set amount=b.amount from #t1 t inner join #t2 b on t.code=b.code and t.dt=b.dt and b.amount<t.amount
update t set amount=0 from #t1 t where not exists(select 1 from #t2 where code=t.code and dt=t.dt)
select * from #t1
/*
id          code       dt                      amount
----------- ---------- ----------------------- -----------
1           a01        2011-09-15 00:00:00.000 15
2           a01        2011-09-15 00:00:00.000 10
3           a01        2011-09-12 00:00:00.000 0
4           a01        2011-09-14 00:00:00.000 15
5           a01        2011-09-14 00:00:00.000 18
6           a02        2011-09-12 00:00:00.000 0
7           a02        2011-09-14 00:00:00.000 15
8           a02        2011-09-14 00:00:00.000 20

(8 行受影响)
*/

go
drop table #t1,#t2

[苏州牛恋歌]

2楼正解啊!

[dawugui]

update #t1 set amount = t2.amount from #t1 t1 , #t2 t2 where  t2.code = t1.code and t2.dt = t1.dt and t2.amount > t1.amount 
update #t1 set amount = 0 from #t1 t1 where not exists(select 1 from #t2 t2 where t2.code = t1.code and t2.dt = t1.dt)

select * from #t1

/*

id          code       dt                                                     amount      
----------- ---------- ------------------------------------------------------ ----------- 
1           a01        2011-09-15 00:00:00.000                                20
2           a01        2011-09-15 00:00:00.000                                15
3           a01        2011-09-12 00:00:00.000                                0
4           a01        2011-09-14 00:00:00.000                                18
5           a01        2011-09-14 00:00:00.000                                30
6           a02        2011-09-12 00:00:00.000                                0
7           a02        2011-09-14 00:00:00.000                                20
8           a02        2011-09-14 00:00:00.000                                30

（所影响的行数为 8 行）

*/

[-晴天]

写成一条:

create table #t1(id int identity,code varchar(10),dt datetime,amount int)
insert into #t1(code,dt,amount)
select 'a01','2011-09-15',20 union all
select 'a01','2011-09-15',10 union all
select 'a01','2011-09-12',2 union all
select 'a01','2011-09-14',15 union all
select 'a01','2011-09-14',30 union all
select 'a02','2011-09-12',2 union all
select 'a02','2011-09-14',15 union all
select 'a02','2011-09-14',30
create table #t2(id int identity,code varchar(10),dt datetime,amount int)
insert into #t2(code,dt,amount)
select 'a01','2011-09-15',15 union all
select 'a01','2011-09-16',2 union all
select 'a01','2011-09-14',18 union all
select 'a02','2011-09-14',20
go
update t set amount=(case when b.amount>t.amount then t.amount when b.amount is null then 0 else b.amount end)
from #t1 t left join #t2 b on t.code=b.code and t.dt=b.dt-- and b.amount<t.amount

select * from #t1
/*
id          code       dt                      amount
----------- ---------- ----------------------- -----------
1           a01        2011-09-15 00:00:00.000 15
2           a01        2011-09-15 00:00:00.000 10
3           a01        2011-09-12 00:00:00.000 0
4           a01        2011-09-14 00:00:00.000 15
5           a01        2011-09-14 00:00:00.000 18
6           a02        2011-09-12 00:00:00.000 0
7           a02        2011-09-14 00:00:00.000 15
8           a02        2011-09-14 00:00:00.000 20

(8 行受影响)
*/

go
drop table #t1,#t2

[dawugui]

[Quote=引用 3 楼 jyh070207 的回复:]
是说反了,
如有对应的code及dt而且#t2.amount <= #t1.amount 则将#t1.amount 改为#t2.amount,
大于的不改
注意,在#t1同一code及dt可能有多条amount不同的记录存在,
[/Quote]

update #t1 set amount = t2.amount from #t1 t1 , #t2 t2 where  t2.code = t1.code and t2.dt = t1.dt and t2.amount < t1.amount 
update #t1 set amount = 0 from #t1 t1 where not exists(select 1 from #t2 t2 where t2.code = t1.code and t2.dt = t1.dt)

select * from #t1
/*

id          code       dt                                                     amount      
----------- ---------- ------------------------------------------------------ ----------- 
1           a01        2011-09-15 00:00:00.000                                15
2           a01        2011-09-15 00:00:00.000                                10
3           a01        2011-09-12 00:00:00.000                                0
4           a01        2011-09-14 00:00:00.000                                15
5           a01        2011-09-14 00:00:00.000                                18
6           a02        2011-09-12 00:00:00.000                                0
7           a02        2011-09-14 00:00:00.000                                15
8           a02        2011-09-14 00:00:00.000                                20

（所影响的行数为 8 行）

*/

[--小F--]

update
  a
set
  amount=case when b.code is null then 0 
              when b.amount<a.amount then b.amount else a.amount end
from
   t1 a ,t2 b
where
   a.code=b.code
and
   a.dt=b.dt

[wynlc]

学习一下

[jyh070207]

谢谢各位!