Duplicate subquery alias error when WHERE clause uses Subquery result

SQL++
1

Hello,

I have a few documents like below
Key key::segment::0::1000
Value {“last”: 0}

and following index

CREATE INDEX `index_key_segment_id` ON `commonVE`.`ve`.`catalog` (META().id)
WHERE META().id LIKE("key::segment::%");

I try to update “last” attribute in the document which has the smallest meta().cas value

UPDATE commonVE.ve.catalog
SET last = CLOCK_MILLIS()
WHERE META().id IN (
    SELECT META().id
    FROM commonVE.ve.catalog
    WHERE META().id LIKE("key::segment::%")
    ORDER BY META().cas ASC
    LIMIT 1)  RETURNING *

I’m getting error below
“code”: 4020,
“msg”: “Duplicate subquery alias catalog”,

Any advise on how could I fix the query?

2

Due to Correlation and No-Schema at present alias must be unqiue in whole query

UPDATE commonVE.ve.catalog AS c
SET c.`last` = CLOCK_MILLIS()
WHERE META(c).id IN (SELECT RAW META(c1).id
                    FROM commonVE.ve.catalog AS c1
                    WHERE META(c1).id LIKE "key::segment::%"
                    ORDER BY META(c1).cas ASC
                    LIMIT 1) RETURNING c.*;
3

Thank you, however now it complains that “No index available”
“code”: 4000,
“msg”: “No index available on keyspace default:commonVE.ve.catalog that matches your query. Use CREATE PRIMARY INDEX ON default:commonVE.ve.catalog to create a primary index, or check that your expected index is online.”,

Should I update existed index:
CREATE INDEX index_key_segment_id ON commonVE.ve.catalog (META().id)
WHERE META().id LIKE(“key::segment::%”);

4

The index error due to UPDATE doesn’t have index on META().id without any index where.

The following might be optimal (Use with request_plus scan_consistency). subquery uses index order scan 1 row and update 1 row

CREATE INDEX ix1 ON commonVE.ve.catalog (META().cas) WHERE META().id LIKE "key::segment::%";

MERGE INTO commonVE.ve.catalog AS m USING (SELECT META(c).id
                    FROM commonVE.ve.catalog AS c
                    WHERE META(c).id LIKE "key::segment::%"
                          AND META(c).cas IS NOT NULL
                    ORDER BY META(c).cas ASC
                    LIMIT 1) AS s
ON KEY s.id
WHEN MATCHED THEN m.`last` = CLOCK_MILLIS() RETURNING m.*;

OR

UPDATE commonVE.ve.catalog AS c USE KEYS (SELECT RAW META(c1).id
                    FROM commonVE.ve.catalog AS c1
                    WHERE META(c1).id LIKE "key::segment::%"
                                AND  META(c1).cas IS NOT NULL
                    ORDER BY META(c1).cas ASC LIMIT 1)
SET c.`last` = CLOCK_MILLIS() RETURNING c.*;
5

Query below works as I needed, appreciate quick and helpful response!

UPDATE commonVE.ve.catalog AS c USE KEYS (SELECT RAW META(c1).id
                    FROM commonVE.ve.catalog AS c1
                    WHERE META(c1).id LIKE "key::segment::%"
                    ORDER BY META(c1).cas ASC LIMIT 1)
SET c.`last` = CLOCK_MILLIS() RETURNING c.*;
1 Like