表结构如下：
CREATE TABLE [dbo].[up1] (
[t1] [int] IDENTITY (1, 1) NOT NULL ,
[t2] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[t3] [char] (3) COLLATE Chinese_PRC_CI_AS NOT NULL,
[t4] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[up2] (
[t2] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[t3] [char] (3) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
表up1中有记录
t1 t2 t3 t4
1 123 a 1
2 456 b 1
3 012 c 2
*****如何写出能实现下面功能的update语句？
如果up2中有记录
t2 t3
abc 1
up1更新为
t1 t2 t3 t4
1 abc 1 1
3 012 c 2
如果up2中有记录
t2 t3
abc 1
def 2
up1更新为
t1 t2 t3 t4
1 abc 1 1
2 def 2 1
3 012 c 2
如果up2中有记录
t2 t3
abc 1
def 2
ghi 3
up1更新为
t1 t2 t3 t4
1 abc 1 1
2 def 2 1
3 012 c 2
4 ghi 3 1
---------------------------------------------------------------
用游标吧!!
---------------------------------------------------------------
DECLARE @I INT
SELECT @I=MAX(T1) FROM UP1
DELECT UP1 WHERE TI<>@I
INSERT INTO UP1(TI,T2,T3,T4)
SELECT CASE WHEN A.T3<@I THEN CAST(A.T3 AS INT) ELSE CAST(A.T3 AS INT)+1 END AS T1,A.T2, A.T3,'1'
FROM UP2 A
---------------------------------------------------------------
--删除多余记录
delete from up1 where t4=1 and t1>(select count() from up2)
--更新记录
select t1 into #temp1 from up1 where t4=1
select t1,t2,t3 into #temp2 from #temp1,up2
update up2 set t2=b.t2,t3=b.t3
from #temp2 b
where t1 =b.t1
--插入记录
select identity(int,1,1) as id ,t2,t3 into #temp3 from up2
insert into up1 (t2,t3,t4) select t2,t3,'1' from #temp3 where #temp3.id>(select count() from up1 where t4=1)
drop table #temp1
drop table #temp2
drop table #temp3