select *, id=identity(int) into #t1 from 表1
select *, id=identity(int) into #t2 from 表2
truncate table 表2
insert 表2
select #t1.工单, #t1.IDY, #t2.IDN
from #t1 join #t2
on #t1.id=#t2.id
drop table #t1
drop table #t2
select *, id=identity(int) into #t2 from 表2
truncate table 表2
insert 表2
select #t1.工单, #t1.IDY, #t2.IDN
from #t1 join #t2
on #t1.id=#t2.id
drop table #t1
drop table #t2
update 表2 as a set(a.IDy) =select b.IDy from 表1 as b where a.工单=b.工单
and a.idn=b.idy+4
from 表a a,表b b
where
(select count(*) from 表a where idy<=a.idy)
=
(select count(*) from 表b where idn<=b.idn)
我不可能重新INSERT表2,因为此表与其他表通过IDN有关联.
zjcxc(邹建) :
你的SQL没有考虑工单号码能行吗?再说在表2中新的工单已经增加了IDY的值,只以前的工单没IDY的值,我想将以前的工单没有IDY值,按IDN顺序与IDY顺序更新.
很明显,两列相差4的情况是偶然出现的。是楼主随便打的测试数据
实现情况根本不可能有这种规律。
我觉得最简单的方法就是临时加一个identity列。(
如果不用identity列的话,重复记录就没办法区分出来
盼高招
*/
create table 表1(工单 varchar(20), IDy int)
create table 表2(工单 varchar(20), IDy int, IDN int)
insert 表1 select '111', 1 union all select '111', 2
union all select '111', 3 union all select '222', 4
union all select '222', 5 union all select '222', 6
insert 表2 select '111', null, 5 union all select '111', null, 6
union all select '111', null, 7 union all select '222', null, 8
union all select '222', null, 9 union all select '222', null, 10
--主要部分
alter table 表2
add id int identity(1, 1)
go
declare @t1 table(id int identity(1, 1), 工单 varchar(20), IDy int)
insert @t1(工单, IDy) select * from 表1update 表2
set IDy=tt.IDy
from @t1 as tt
where 表2.id=tt.idselect 工单, IDy, IDN from 表2alter table 表2
drop column id
go
--清除
drop table 表1
drop table 表2
是的,上面只我举例而也,两列相差4的情况不是实际情况,但是表2的记录并不等于表1的记录,只是通过工单相关联出的记录相等,不知是我描述不清楚,还是大家没理解我的需求
我再说明一下两表这间的关系如下:表1:
工单 IDY
020214116 78629
020214116 78630
020214116 78631
020214116 78632
020214116 78633
020214116 78634
020214116 78635
表2:
工单 IDN IDY
020214116 75740 NULL
020214116 75741 NULL
020214116 75742 NULL
020214116 75743 NULL
020214116 75744 NULL
020214116 75745 NULL
020214116 75746 NULLIDY在表1已经是唯一且identity(1, 1)
IDN在表2已经是唯一且identity(1, 1)
其他表通过IDN进行关联,所以不能重倒
and a.idn=b.idy-78629+75740如果楼主要从某一行开始比如10000行那么update a set a.idy=b.idy from 表2 a,表1 b where a.工单=b.工单
and a.idn=b.idy-78629+75740 and a.IDN>10000
insert into a select 020214116, 78629 union
select 020214116, 78630 union
select 020214116, 78631 union
select 020214116, 78632 union
select 020214116, 78633 union
select 020214116, 78634 union
select 020214116, 78635 create table b(gd varchar(10) , idn int,idy int )
insert into b
select 020214116, 75739 ,0 union
select 020214116, 75740 ,null union
select 020214116, 75741 ,null union
select 020214116, 75742 ,null union
select 020214116, 75743 ,null union
select 020214116, 75744 ,null union
select 020214116, 75745 ,null union
select 020214116, 75746 ,null union
select 020214116, 75747 ,9select (select count(*) from a where idy<=aa.idy ),aa.* from a aaupdate b set b.idy = a.idy
from a a
where
b.idy is null and
a.gd = b.gd and
(select count(*) from a tempA where tempA.idy<=a.idy )
=
(select count(*) from b tempB where tempB.idn<=b.idn and tempB.idy is null)
select * from b
go
drop table a,b/* 结果
gd idn idy
---------- ----------- -----------
20214116 75739 0
20214116 75740 78629
20214116 75741 78630
20214116 75742 78631
20214116 75743 78632
20214116 75744 78633
20214116 75745 78634
20214116 75746 78635
20214116 75747 9
*/
上面只是举例而也.
IDY和IDN之间有什么实际的关系?
表1和表2之间什么关系,他们数据等量吗?
需要组合成什么样的数据?
按你的路在我的表中找不出记录,但在两表中实际是有的
update b set b.idy = a.idy
from a as a1,b as b1
where
a1.gd = b1.gd and
(select count(*) from a tempA where tempA.idy<=a1.idy and tempA.gd=a1.gd )
=
(select count(*) from b tempB where tempB.idn<=b1.idn and tempB.gd = b1.gd)
go
create table #b1
(工单 varchar(8),
IDy int)
insert #b1
select '111',1
union select '111',2
union select '111',3
union select '222',4
union select '222',5
union select '222',6
go
create table #b2
(工单 varchar(8),
IDy int default 0,
IDN int)
insert #b2(工单,IDN)
select '111',5
union select '111',6
union select '111',7
union select '222',8
union select '222',9
union select '222',10
go
drop table #t2
drop table #t1
select *, id=identity(int) into #t1 from #b1
select *, id=identity(int) into #t2 from #b2
select #t1.工单, #t1.IDY, #t2.IDN
from #t1 join #t2
on #t1.id=#t2.id