update t_inf_tmp tmp set (orignal_useragent, orignal_date)=
(
select useragent, insertdate from(
select useragent, insertdate
from v_his_all his
where his.isdn=tmp.isdn
and his.insertdate<tmp.insertdate
order by his.insertdate desc
)
where rownum<=1
);
想将临时表中orignal_useragent, orignal_date更新为最近一次的值。上面的sql在oracle中执行完，都更新成v_his_all最大时间对应的值了。我想要的是相应号码的。求大神指点？
------最佳解决方案--------------------
上面别名写错了
update t_inf_tmp tmp set (orignal_useragent, orignal_date)=(
select useragent, insertdate from(
select useragent, insertdate, rank() over(partition by isdn order by insertdate desc) rk
from v_his_all his1
where his1.isdn=tmp.isdn and his1.insertdate<tmp.insertdate
) his2
where his2.isdn=tmp.isdn and rk<=1
);
------其他解决方案--------------------
帮顶，哈哈，
------其他解决方案--------------------
update t_inf_tmp tmp set (orignal_useragent, orignal_date)=
(
select useragent, insertdate from(
select useragent, insertdate, rank() over(partition by isdn order by insertdate desc) rk
from v_his_all
where his.isdn=tmp.isdn and his.insertdate<tmp.insertdate
) his
where his.isdn=tmp.isdn and rk<=1
);
------其他解决方案--------------------
t_inf_tmp后面跟上别名tmp，在本地可以执行，为什么在服务器上执行，就报tmp.isdn标识符无效呢？
------其他解决方案--------------------
可能是ORACLE版本不同，低版本的ORACLE不支持太多层的嵌套表使用别名，会找不到，需要稍作修改减少嵌套语句的层数