我只需要Oracle sdo_nn Update语句中的一个唯一结果, [英] I need only one unique result in Oracle sdo_nn Update sentence ,
问题描述
我只需要一个唯一的结果,从tableB.Field到tableA.Field我正在使用sdo运算符sdo_nn,这是代码:
I need Only one unique result from tableB.Field to tableA.Field I am using sdo operator sdo_nn, this is the code:
UPDATE table1 t1
SET t1.fieldA = (SELECT T2.fieldB,SDO_NN_DISTANCE(1) distance
FROM table1 T1, table2 T2
WHERE
(sdo_nn(t1.geometry,t2.geometry,'SDO_NUM_RES=1',1)= 'TRUE')
ORDER BY DIST
)
WHERE EXISTS(
SELECT 1
FROM table2 t2
WHERE sdo_nn(t1.geometry, t2.geometry,'SDO_NUM_RES=1',1)='TRUE'
AND(t2.cell_name = 'string1' or t2.cell_name = string2')AND t1.fieldA = NULL
);
在子查询的选择语句中,我得到一个错误,因为我仅使用一个字段(t1.fieldA),但是在语句中,我使用了运算符 SDO_NN_DISTANCE(1) 和sql developer将这个运算符计为另一个字段.写这句话的正确方法是什么?我只使用sql,因为我需要将此代码插入vba
In the select sentence of the subquery i get an error because i only use one field(t1.fieldA), but in the sentence i use the operator SDO_NN_DISTANCE(1) and the sql developer count this operator like another field. What is the correct way to write this sentence? I only use sql because i need to insert this code in vba
谢谢!
推荐答案
很显然,您不能(简化)
Obviously, you can't (simplified)
set t1.fieldA = (t2.fieldB, distance) --> you want to put two values into a single column
因此,从使用分析函数( row_number
)进行排序"的子查询中,单独获取 fieldB
.按 sdo_nn_distance(1)desc
进行的行;然后获取第一行的 fieldB
值.
Therefore, get fieldB
alone from the subquery which uses analytic function (row_number
) to "sort" rows by sdo_nn_distance(1) desc
; then get the first row's fieldB
value.
类似这样的东西(希望我设置正确的括号):
Something like this (I hope I set the parenthesis right):
UPDATE table1 t1
SET t1.fieldA =
(SELECT x.fieldB --> only fieldB
FROM (SELECT T2.fieldB, --> from your subquery
SDO_NN_DISTANCE (1) distance,
ROW_NUMBER ()
OVER (ORDER BY sdo_nn_distance (1) DESC) rn
FROM table1 T1, table2 T2
WHERE (sdo_nn (t1.geometry,
t2.geometry,
'SDO_NUM_RES=1',
1) = 'TRUE')) x
WHERE rn = 1) --> where RN = 1
WHERE EXISTS
(SELECT 1
FROM table2 t2
WHERE sdo_nn (t1.geometry,
t2.geometry,
'SDO_NUM_RES=1',
1) = 'TRUE'
AND ( t2.cell_name = 'string1'
OR t2.cell_name = 'string2')
AND t1.fieldA IS NULL);
这篇关于我只需要Oracle sdo_nn Update语句中的一个唯一结果,的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!