如题
表结构和数据如下:
id PIK_CODE PIK_YEAR PIK_MONTH PIK_NUM
1 AAA 2009 1 111
2 AAA 2009 2 222
3 AAA 2009 3 333
4 AAA 2009 4 444
5 BBB 2009 1 NULL
6 BBB 2009 2 NULL
7 BBB 2009 3 NULL
8 BBB 2009 4 NULL原始数据是前4行,首先执行一个方法复制一套新的PIK_YEAR和PIK_MONTH相同,并存入了一个新的PIK_CODE
现在要做的是更新PIK_NUM,把PIK_CODE='AAA'的相对应的PIK_NUM更新到PIK_CODE='BBB'的相对应的PIK_NUM
条件是PIK_YEAR和PIK_MONTH相同.在存储过程中实现,是否有简单的方法?(现实表中更新的字段还有很多,条件也有很多)在线等,50分笑纳!!!
表结构和数据如下:
id PIK_CODE PIK_YEAR PIK_MONTH PIK_NUM
1 AAA 2009 1 111
2 AAA 2009 2 222
3 AAA 2009 3 333
4 AAA 2009 4 444
5 BBB 2009 1 NULL
6 BBB 2009 2 NULL
7 BBB 2009 3 NULL
8 BBB 2009 4 NULL原始数据是前4行,首先执行一个方法复制一套新的PIK_YEAR和PIK_MONTH相同,并存入了一个新的PIK_CODE
现在要做的是更新PIK_NUM,把PIK_CODE='AAA'的相对应的PIK_NUM更新到PIK_CODE='BBB'的相对应的PIK_NUM
条件是PIK_YEAR和PIK_MONTH相同.在存储过程中实现,是否有简单的方法?(现实表中更新的字段还有很多,条件也有很多)在线等,50分笑纳!!!
select 'BBB', PIK_YEAR,PIK_MONTH,PIK_NUM
from tb where PIK_CODE='AAA'
如果id自增的话
from tb t1,tb t2
where t1.PIK_YEAR=t2.PIK_YEAR and t1.PIK_MONTH=t2.PIK_MONTH
and T1.PIK_NUM Is Null???
update t
set PIK_NUM=s.PIK_NUM
from tb t join tn s on s.PIK_YEAR=t.PIK_YEAR and s.PIK_MONTH=t.PIK_MONTH
where t.PIK_CODE ='BBB' and s.PIK_CODE ='AAA'
这样?
a set PIK_NUM = b.PIK_NUM
from
(select* from tb where PIK_NUM is not null) a
join
(select * from tb where PIK_NUM is null) b
on
a.PIK_YEAR=b.PIK_YEAR and t1.PIK_MONTH=t2.PIK_MONTH
a set PIK_NUM = b.PIK_NUM
from
(select* from tb where PIK_NUM is not null) a
join
(select * from tb where PIK_NUM is null) b
on
a.PIK_YEAR=b.PIK_YEAR and a.PIK_MONTH=b.PIK_MONTH
(select * from tb where pik_code='aaa')t
where tb.pik_code='bbb' and t.pik_month=tb.pik_month结果:
-------------------------------
1 aaa 2009 1 111
2 aaa 2009 2 222
3 aaa 2009 3 333
4 aaa 2009 4 444
5 bbb 2009 1 111
6 bbb 2009 2 222
7 bbb 2009 3 333
8 bbb 2009 4 444 -------------------------------
不知道你是不是想要的这个结果
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-05 21:34:52
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[PIK_CODE] varchar(3),[PIK_YEAR] int,[PIK_MONTH] int,[PIK_NUM] int)
insert [tb]
select 1,'AAA',2009,1,111 union all
select 2,'AAA',2009,2,222 union all
select 3,'AAA',2009,3,333 union all
select 4,'AAA',2009,4,444 union all
select 5,'BBB',2009,1,null union all
select 6,'BBB',2009,2,null union all
select 7,'BBB',2009,3,null union all
select 8,'BBB',2009,4,null
--------------开始查询--------------------------
update
a set PIK_NUM = b.PIK_NUM
from
(select* from tb where PIK_NUM is null) a
join
(select * from tb where PIK_NUM is not null) b
on
a.PIK_YEAR=b.PIK_YEAR and a.PIK_MONTH=b.PIK_MONTHselect * from tb
----------------结果----------------------------
/*id PIK_CODE PIK_YEAR PIK_MONTH PIK_NUM
----------- -------- ----------- ----------- -----------
1 AAA 2009 1 111
2 AAA 2009 2 222
3 AAA 2009 3 333
4 AAA 2009 4 444
5 BBB 2009 1 111
6 BBB 2009 2 222
7 BBB 2009 3 333
8 BBB 2009 4 444(8 行受影响)
*/
update tb set pik_num=t.pik_num
from (select * from tb where pik_num is not null)t where tb.pik_num is null and tb.pik_year=t.pik_year and tb.pik_month=t.pik_month
我自己多添加了几条资料
----------------------------------------
1 AAA 2009 1 111
2 AAA 2009 2 222
3 AAA 2009 3 333
4 AAA 2009 4 444
5 BBB 2009 1 111
6 BBB 2009 2 222
7 BBB 2009 3 333
8 BBB 2009 4 444
1 ccc 2009 1 111
2 ccc 2009 2 222
3 ccc 2009 3 333
4 ccc 2009 4 444
5 ddd 2009 1 111
6 ddd 2009 2 222
7 ddd 2009 3 333
8 ddd 2009 4 444
------------------------------------
刚试了下小F的语句,提示: 衍生資料表 'a' 無法更新,因為衍生資料表的某個資料行是衍生的或常數。
怎么解决,,,小F...
declare @tb table (id int,pik_code nvarchar(10),
pik_year int,pik_month int,pik_num int)
insert into @tb select 1,'aaa','2009',1,111
union all select 2,'aaa','2009',2,222
union all select 3,'aaa','2009',3,333
union all select 4,'aaa','2009',4,444
union all select 5,'aaa','2009',1,null
union all select 6,'aaa','2009',2,null
union all select 7,'aaa','2009',3,null
union all select 8,'aaa','2009',4,null
update a set a.pik_num=b.pik_num
from @tb a join (select * from @tb where pik_num is not null) b
on a.pik_year=b.pik_year and a.pik_month=b.pik_month
where a.pik_num is null
select * from @tb id pik_code pik_year pik_month pik_num
----------- ---------- ----------- ----------- -----------
1 aaa 2009 1 111
2 aaa 2009 2 222
3 aaa 2009 3 333
4 aaa 2009 4 444
5 aaa 2009 1 111
6 aaa 2009 2 222
7 aaa 2009 3 333
8 aaa 2009 4 444(8 行受影响)