表1:
wareid ,waredesc ,date1
001 厂房 null
002 外包1 null
003 租用1 null
004 世纪联华 null
005 租用2 null
表2
wareid ,date2
001 2005-04-05
001 2005-04-06
001 2005-04-07
001 2005-04-08
001 2005-04-09
002 2005-04-05
002 2005-04-06
002 2005-04-07
003 2005-04-08
003 2005-04-09表2中几记录wareid的时间,
现在想要根据表2修改表一,把表1的date1 修改为表2中最大日期。
由于表1中的有些wareid在表2中是没有的,没有就不用修改
wareid ,waredesc ,date1
001 厂房 null
002 外包1 null
003 租用1 null
004 世纪联华 null
005 租用2 null
表2
wareid ,date2
001 2005-04-05
001 2005-04-06
001 2005-04-07
001 2005-04-08
001 2005-04-09
002 2005-04-05
002 2005-04-06
002 2005-04-07
003 2005-04-08
003 2005-04-09表2中几记录wareid的时间,
现在想要根据表2修改表一,把表1的date1 修改为表2中最大日期。
由于表1中的有些wareid在表2中是没有的,没有就不用修改
Set date1=date2 from
(select wareid,max(date2)as date2 from 表2 group by wareid) T
where 表1.wareid=T.wareid
set date1=maxdate
from (select max(date2) as maxdate ,wareid from 表2 group by wareid ) A
inner 表1 B on A.wareid =B.wareid
set date1=maxdate
from (select max(date2) as maxdate ,wareid from 表2 group by wareid ) A
inner join 表1 B on A.wareid =B.wareid
update 表1 set date1 = (select max(date2) from 表2 where wareid = ID and wareid is not null)
FETCH NEXT FROM
......
select distinct max(date2),wareid from 表2 group by wareid
declare @date2 datetime
declare @wareid int
open Fee
fetch next from Fee into @date2,@wareid
while @@fetch_status = 0
begin
----------------------------------------------------------
if not exists(select * from 表1
where companycode = @CompanyCode
and wareid=@wareid begin
update 表1 set date1 = @date2
end
fetch next from Fee into @date2,@wareid
end
close Fee
deallocate Fee
set date1=b.date2
from 表1 a inner join (select wareid,max(date2) as date2 from 表2 group by wareid)
b
on a.wareid=b.wareid
insert @tb
select '001','厂房',null union all
select '002','外包1',null union all
select '003','租用1' ,null union all
select '004','世纪联华',null union all
select '005','租用2',null
declare @tbb table(wareid varchar(10),date2 datetime)
insert @tbb
select '001','2005-04-05' union all
select '001','2005-04-06' union all
select '001','2005-04-07' union all
select '001','2005-04-08' union all
select '001','2005-04-09' union all
select '002','2005-04-05' union all
select '002','2005-04-06' union all
select '002','2005-04-07' union all
select '003','2005-04-08' union all
select '003','2005-04-09'update @tb set date1=maxdate from
(select max(date2) as maxdate,wareid from @tbb group by wareid)a inner join @tb b on a.wareid = b.wareidselect * from @tb/*
测试结果
wareid waredesc date1
---------- ---------- ------------------------------------------------------
001 厂房 2005-04-09 00:00:00.000
002 外包1 2005-04-07 00:00:00.000
003 租用1 2005-04-09 00:00:00.000
004 世纪联华 NULL
005 租用2 NULL
*/
use pubs
--------------------------------------------------
--测试用数据
create table 表1(wareid varchar(10),waredesc varchar(20),date1 smalldatetime)
insert 表1 select '001','厂房',null
union all select '002','外包1',null
union all select '003','租用1',null
union all select '004','世纪联华',null
union all select '005','租用2',nullcreate table 表2(wareid varchar(10),date2 smalldatetime)
insert 表2 select '001','2005-04-05'
union all select '001','2005-04-06'
union all select '001','2005-04-07'
union all select '001','2005-04-08'
union all select '001','2005-04-09'
union all select '002','2005-04-05'
union all select '002','2005-04-06'
union all select '002','2005-04-07'
union all select '003','2005-04-08'
union all select '003','2005-04-09'
select * from 表1
select * from 表2
--------------------------------------------------
--查询语句
update 表1 set date1=(select max(表2.date2) from 表2 where 表1.wareid=表2.wareid)
select * from 表1
go--删除测试用表
drop table 表1,表2
go
wareid waredesc date1
---------- -------------------- ------------------------------------------------------
001 厂房 2005-04-09 00:00:00
002 外包1 2005-04-07 00:00:00
003 租用1 2005-04-09 00:00:00
004 世纪联华 NULL
005 租用2 NULL(所影响的行数为 5 行)