求一较高难度的SQL语句：实现Update两个Select搭配的查询结果

[gjw310]

我想更新两个select语句搭配起来的一个查询结果，如

update (select send_status,op_time from
(select send_status,op_time from temp_wsndcmdday where send_status=0 order by send_time)
where rownum<3) a set a.send_status='1',a.op_time='sysdate'

这样一些就报错，使用工具是Toad for Oracle，数据库是Oracle，请高手帮助这个sql语句该怎么写？

[htl258_Tony]

oracle不熟,帮顶

[gjw310]

这个SQL语句语法还是存在问题，不能执行。rownum放到里面和外头还不一样，我想要取的数据是先order by排序后的前三行，而不是前三行后排序，能否再想想，谢谢

[gjw310]

我想要在原表中修改符合条件的那两条记录，在临时表中修改了也没有用

[gjw310]

继续等好心高手...

[dawugui]

请给出表结构,测试数据,相关算法和需要的结果.谢谢!

[gjw310]

在线等高手

[dawugui]

update temp_wsndcmdday
set send_status='1' , 
    op_time='sysdate' 
from temp_wsndcmdday m where send_status = 0 and 
exists (select 1 from (select send_status , op_time from temp_wsndcmdday where send_status = 0 and rownum < 3 order by send_time) n
where n.send_status = m.send_status and n.op_time = m.op_time )

[Zoezs]

[Quote=引用楼主 gjw310 的帖子:]
我想更新两个select语句搭配起来的一个查询结果，如

update (select send_status,op_time from
(select send_status,op_time from temp_wsndcmdday where send_status=0 order by send_time)
where rownum <3) a set a.send_status='1',a.op_time='sysdate'

这样一些就报错，使用工具是Toad for Oracle，数据库是Oracle，请高手帮助这个sql语句该怎么写？
[/Quote]

select send_status,op_time into #temp from 
      (select send_status,op_time from temp_wsndcmdday where send_status=0 order by send_time) 
  where rownum <3

update #temp set send_status='1',op_time='sysdate' 

[dawugui]

[Quote=引用 2 楼 dawugui 的回复:]
SQL codeupdate temp_wsndcmdday
set send_status='1' ,
op_time='sysdate'
from temp_wsndcmdday m where send_status = 0 and
exists (select 1 from (select send_status , op_time from temp_wsndcmdday where send_status = 0 and rownum < 3 order by send_time) n
where n.send_status = m.send_status and n.op_time = m.op_time )
[/Quote]

保险起见把send_time加上.

update temp_wsndcmdday
set send_status='1' ,
op_time='sysdate'
from temp_wsndcmdday m where send_status = 0 and
exists (select 1 from (select send_status , op_time , send_time from temp_wsndcmdday where send_status = 0 and rownum < 3 order by send_time) n
where n.send_status = m.send_status and n.op_time = m.op_time and n.send_time = m.send_time )

[gjw310]

那个select 1 中的1改成具体两个字段，在Toad中依然报错，提示如下：
The Instant Message Index file, D:\PROGRA~1\QUESTS~1\TOADFO~1\KXMsgs.ini , is corrupted, please contact support.

[gjw310]

update temp_wsndcmdday
set send_status = '1' ,
op_time = sysdate
from temp_wsndcmdday
---------------------------------
这样就有问题啊，update后面好像就不能跟from

[gjw310]

[Quote=引用 10 楼 dawugui 的回复:]
SQL code
请给出表结构,测试数据,相关算法和需要的结果.谢谢!
[/Quote]

Column Name ID Pk Null? Data Type
----------------------------------------------------
OP_TIME 15 N DATE
SEND_STATUS 21 N CHAR (1 Byte)
SEND_TIME 22 N DATE
----------------------------------------------------

[gjw310]

想要结果：
查询出状态是send_status=0的，按send_time排序后（结果有几百条），取离现在时间最远的2条，然后把这两条记录的send_status字段改为“1”，把op_time设为当前。

[gjw310]

[Quote=引用 11 楼 fcuandy 的回复:]
sqlserver2000(2005)下
set rowcount 3
update a set a.send_status=1 ,a.op_time='sysdate' from temp_wsndcmmday a where send_status=0 with(index=send_time上的索引名)
set rowcount 0

或者用

update top (3) ..... with(index=..)

sqlserver2005下

with fc as
(
select top 3 * from 表 wehre send_status=0 order by send_time
)
update fc set .....

即可
[/Quote]
这是在Oracle数据库下，用的是Toad for Oracle工具。

[dawugui]

update temp_wsndcmdday
set send_status = '1' , 
    op_time = sysdate
from temp_wsndcmdday a where a.send_status = 0 and a.op_time in 
(
  select op_time from 
  (
    select send_status , op_time , row_number(order by send_time) px from temp_wsndcmdday where send_status = 0 order by send_time 
  ) t where px <= 2
)

update temp_wsndcmdday
set send_status = '1' , 
    op_time = sysdate
from temp_wsndcmdday a where exists (select 1 from
(
  select * from 
  (
    select send_status , op_time , row_number(order by send_time) px from temp_wsndcmdday where send_status = 0 order by send_time 
  ) t where px <= 2
) b where b.send_status = a.send_status and b.op_time = a.op_time
)

[fcuandy]

sqlserver2000(2005)下
set rowcount 3
update a set a.send_status=1 ,a.op_time='sysdate' from temp_wsndcmmday a where send_status=0 with(index=send_time上的索引名)
set rowcount 0

或者用

update top (3) ..... with(index=..)

sqlserver2005下

with fc as
(
select top 3 * from 表 wehre send_status=0 order by send_time
)
update fc set .....

即可