请教一个MS SQL的SQL语句 update(100分)

G

Geminiyc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #1

我的表格如下，4个字段，6条记录（假设）——
ID NUM1 NUM2 RQ
1 0.1 080301
2 0.2 080301
3 0.3 080301
---------
1 0 0 080302
2 0 0 080302
4 0 0 080302
现在准备把3月2号的num2设置为3月1号的num1(ID号要相同)，该如何用一个update语句来实现。即把第4/5条的num2设置为0.1/0.2，谢谢

 

D

dgtg0710

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #2

update table set num2 = (select num1 from table where id = a.id and rq = '080301')
where rq = '080302'
and id in (select id from table where rq='080301')

 

A

Adao213

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #3

update table set a.num2=b.num1
from table a,(select * from table where rq='080301') b
where a.rq='080302' and a.id=b.id

 

A

Adao213

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #4

上面的代码没有测试 ,测试后发现有问题(sql server2000里应该没有问题),改成如下.
===============================
update test123
set test123.num2=b.num1
from test123,(select id,num1 from test123 where rq='080301') as b where
test123.rq='080302' and test123.id=b.id

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #5

update b set b.num2=a.num1 from (select id,num1 from test123 where rq='080301') A, (select id,num1 from test123 where rq='080302') B WHERE A.ID=B.ID

 

G

Geminiyc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #6

对的哈，谢谢二位。我采用的是后一种方法。

 

J

jiangxidna

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #7

恩，分点分
哈哈

 

G

Geminiyc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-06-28

• #8

多人接受答案了。