求一简单SQL语句

[lj1006]

表TABLE1（name,date,dj）
A,2007-1-1,20.56
A,2007-1-1,21
A,2007-2-1,21
A,2007-2-1,20


表TABLE2（name,dj）
A，NULL

我想把 表TABLE2中name为A的字段dj更新为 表TABLE1中name为A date取最大的其中任何一个dj即可

即想将TABLE2（name,dj）更新为：
A，21 或 A，20

请问用一句SQL可否完成更新工作呢，谢谢！！

[hellowork]

update a set name = (select max(dj) from table1 where name = a.name)
from table2 as a

[chuifengde]

declare @a table(name varchar(10),date smalldatetime,dj numeric(10,2))
insert @a select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20

declare @b table(name varchar(10),dj numeric(10,2))
insert @b select 'A',null

update s set
dj=(select top 1 dj from @a where name=gg.name and [date]=gg.[date]) from @b s
left join (select name,max([date]) [date] from @a group by name) gg
on s.name=gg.name

select * from @b

[hellowork]

抱歉,更正一下
update a set dj = (select max(dj) from table1 where name = a.name)
from table2 as a

----或者
update a set dj = b.name
from table2 as a
inner join (select name, max(dj) as dj from table1 group by name) as b
on a.name = b.name

[lj1006]

不是max(dj)，是max(date) 中的一个dj

[echiynn]

create table t1(
name varchar(10),
date varchar(10),
dj decimal(10,2))

insert t1 select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20


create table t2(
name varchar(10),
dj decimal(10,2))

insert t2 select 'A',null
---------------------------

update t2
set dj = t.dj
from t1,
(
select name, max(dj) dj
from t1 a
where not exists (select 1 from t1 where date > a.date)
group by name
) t
where t1.name = t.name

---------------------------
select * from t1
drop table t1, t2

/*
name dj
---------- ------------
A 21.00

（所影响的行数为 1 行）
*/

[昵称被占用了]

好像可以不用管具体取哪个

update a
set dj=b.dj
from table2 a,table1 b
where a.name=b.name
and not exists (
select 1 from table1
where name=b.name
and date>b.date
)

[wolf5200]

如果TABLE2中只有A，以上可以使用。但是还有B...其他名的话就不能使用了


insert T select 'A','2007-07-02',23
union all
select 'B','2007-07-02',233
create table TT(id varchar(10),dt datetime,fs int)
insert TT select 'A','2007-07-02',23
union all
select 'A','2007-07-02',233
union all
select 'A','2007-07-02',21
union all
select 'A','2007-07-02',22



update a set a.fs=(select max(fs) from TT t where t.id=a.id ) from T as a,TT as aa where a.id=aa.id

[sgucxc0]

declare @a table(name varchar(10),date smalldatetime,dj numeric(10,2))
insert @a select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20

declare @b table(name varchar(10),dj numeric(10,2))
insert @b select 'A',null

--不是max(dj)，是max(date) 中的一个dj

update a set dj = (select max(dj) from @a where [name] = a.name and date in(select max(date) from @a where [name] = a.name))
from @b as a

select * from @b
/* 结果
A 21.00
*/

[紫气东来_999]

update a set dj = (select max(dj) from @a where [name] = a.name and date in(select max(date) from @a where [name] = a.name))
from @b as a


子查询之子查询，赞楼上的~

[liufuyahong]

create table #t1
(
name varchar(10),
date datetime,
dj float
)

go

create table #t2
(
name varchar(10),
dj float
)
go

insert into #t1
select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20

insert into #t2
select 'A',null

update #t2
set dj=(
select top 1 dj from #t1 O
where name=#t2.name
and date=(select max(date) from #t1
where name=O.name)
order by newid()
)

select * from #t2

drop table #t1
drop table #t2

[hnkdzhanghui]

update #t2 set dj=#t1.dj from #t1 , #t2 where date=(select max(date) from #t1 where #t1.name=#t2.name) and #t1.name=#t2.name

[ojuju10]

update b set b.dj=a.dj from (select top 1 * from table1 a
where not exists(select 1 from table1 a.name=name and a.date<date)
order by newid()) a ,table2 b
where a.name=b.name

[ojuju10]

create table table1(name varchar(10),date smalldatetime,dj numeric(10,2))
insert table1 select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20

create table table2(name varchar(10),dj numeric(10,2))
insert table2 select 'A',null

update b set b.dj=a.dj from (select top 1 * from table1 a
where not exists(select 1 from table1 where a.name=name and a.date<date)
order by newid()) a ,table2 b
where a.name=b.name

select * from table2