现在有两个表:device和devicepf。 device是原始数据表,其中一个字段是speed,它所有的值都是0。 devicepf是新得到的数据,它里面speed字段都是最新得到的数据。所以现在我想用devicepf.speed 将 device.speed更新了(通过deviceid进行关联 )。 但是有一个问题:devicepf 中不是所有设备,device中存放的才是所有的设备,所以我用如下语句更新会有问题:
update device set device.speed =
(
select speed from devicepf where
devicepf.deviceid = device.deviceid and
)
会把null更新给device ! 请问这样的语句怎么写呀? 谢谢!
update device set device.speed =
(
select speed from devicepf where
devicepf.deviceid = device.deviceid and
)
会把null更新给device ! 请问这样的语句怎么写呀? 谢谢!
我也想知道答案...
(
select nvl(speed,0) from device,devicepf where
device.deviceid = devicepf.deviceid(+)
)
(
select a.speed as aspeed,b.speed as bspeed
from device a,devicepf b
where a.deviceid = b.deviceid and b.spped is no null
)
set aspeed = bspeed注:deviceid必须是关键字段!
因为不是查出了null值而是从devicepf表中没有找到对应的数据。
update device set device.speed = nvl(
(
select speed from device,devicepf where
device.deviceid = devicepf.deviceid(+)
),0)
天知道行不行
(select b.speed from devicepf b where b.deviceid = a.deviceid)
where a.deviceid in(
select deviceid from devicepf);
这样应该可以
devicepf.deviceid = device.deviceid) where exists(select 1 from devicepf where device.deviceid = deviceid); --这条语句可以新方法,试试吧:
update device set device.speed =(select nvl(max(speed),0) from devicepf where
devicepf.deviceid = device.deviceid);
update device set device.speed =(select speed from devicepf where
devicepf.deviceid = device.deviceid) where exists(select 1 from devicepf where device.deviceid = deviceid);
本人一直都是用此语,正确且执行效率高。
devicepf.deviceid = device.deviceid)
where
device.deviceid in (select devicepf.deviceid from devicepf)