Update document with value from other document

SQL++
1

I am trying to update a document with a value from another document, but it doesn’t seem to be working…

update ug01 set sensorId = (select s.sensorId 
from ug01 s 
where s.locationDocKey = meta().id and s.type = 'sensor' and s.tenantId is valued)
where type = 'sensor_location' and tenantId is valued

The “sensor” doc contains the “sensor_location” doc’s ID, so I can do a join of sorts… however, when I try that in the sub query, I get the following error:

Error evaluating SET clause. - cause: FROM in correlated subquery must have USE KEYS clause: FROM ug01.

2

The subquery is correlated due to meta().id . AT present all correlated subqueries requires USE KEYS.
The error is expected.

You can try with MERGE as follows as this is not one to one match but see it satisfies your use case.

MERGE INTO ug01 u
USING (SELECT s.sensorId, s.locationDocKey
                        FROM ug01 AS s
                        WHERE s.type = "sensor" AND s.tenantId IS VALUED AND s.locationDocKey IS VALUED) AS s1
ON KEY s1.locationDocKey
WHEN MATCHED
THEN
UPDATE SET u.sensorId = s1.locationDocKey WHERE u.type = "sensor_location" AND u.tenantId IS VALUED;
2 Likes
3

Worked like a charm, thank you!!