Update Cross Documents

Hi @vsr1 ,

we have two types of docs as below

doc 1 {
“exId” : “exid1”,
“pId”:“pid1”,
“type” : “p”
}

doc2 {
“pId”:“pid1”,
“type” : “i”

}

doc3 {
“pId”:“pid1”,
“type” : “i”

}

there is one doc with type as p and has pId value as unique
there can be n docs with type as i and all have pid same to that of type as p
like p has one to many to i.

We need to copy the exId from the doc type p to each related doc type i. such that end result is

doc2 {
“exId” : “exid1”,
“pId”:“pid1”,
“type” : “i”

}

doc3 {
“exId” : “exid1”,
“pId”:“pid1”,
“type” : “i”

}

Use ANSI merge ANSI JOIN Enhancements and ANSI MERGE | The Couchbase Blog

MERGE INTO default AS m USING (SELECT d.exId, d.pId FROM default AS d WHERE d.type = "p") AS s
ON m.pId = s.pId  AND m.type = "i" AND  m.exId != s.exId
WHEN MATCHED THEN UPDATE m.exId = s.exId;