N1QL How to UPDATE using a subquery?

SQL++
1

I can’t find an example of the correct syntax for updating a document with the results of a subquery:

update `acmaster` set settings = (select showGrid, showNumbers, units from `acmaster` 
where type = 'settings') where meta().id not like '_sync%' and email ='test@example.com' and type='user'

  {
    "code": 4020,
    "msg": "Duplicate subquery alias acmaster",
    "query_from_user": "update `acmaster` set settings = (select showGrid, showNumbers, units from `acmaster` where type = 'settings') where meta().id not like '_sync%' and email ='test@example.com' and type='user'"
  }
]

I’ve tried various permutations and haven’t figured out the correct syntax.

2

In the N1QL FROM source must have unique alias across whole query. In this case UPDATE acmaster , Subquery FROM acmaster inherits same alias. That is the reason it returns error.

UPDATE `acmaster`
  SET settings = 
          (SELECT  s.showGrid, s.showNumbers, s.units 
           FROM  `acmaster` AS s  
          WHERE s.type = "settings") 
WHERE META().id  NOT LIKE "_sync%" AND  email ="test@example.com" AND type="user";
1 Like
3

@vsr1 Thank you for your quick reply. With this syntax settings ends up be assigned a list containing a single object:

"settings": [
        {
          "showGrid": "red-grid",
          "showNumbers": true,
          "units": 2
        }
],

Is it possible to assign the object within the list to settings?

"settings": {
          "showGrid": "red-grid",
          "showNumbers": true,
          "units": 2
        },
4

If subquery generate single array of object you can do [0] like below

   UPDATE `acmaster`
      SET settings = 
              (SELECT  s.showGrid, s.showNumbers, s.units 
               FROM  `acmaster` AS s  
              WHERE s.type = "settings")[0]
    WHERE META().id  NOT LIKE "_sync%" AND  email ="test@example.com" AND type="user";
1 Like
5

Thanks again! I thought I tried that but obviously didn’t…