# Update by query for each field with value by sql query

**URL:** <https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192>\
**Category:** Elasticsearch\
**Created:** [August 2, 2021, 6:46am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192 "2021-08-02T06:46:22Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rammi22](https://avatars.discourse-cdn.com/v4/letter/r/258eb7/32.png) [@Rammi22](https://discuss.elastic.co/u/Rammi22)\
**Post date:** [August 2, 2021, 6:46am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192/1 "2021-08-02T06:46:22Z")

</div>

Hello,

following task:

- get **each document one by one** from elasticserach server
- **add field** to document with **value stored in a database** (MariaDB)

The \_id of the document is the reference to its record in the database:

I think the [`update_by_query` API](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-update-by-query.html) is the right one.

Fantasy code:

```auto
{
  "script": {
    "source": "ctx._source.count++",
    "field.any_ref": // HowTo: my.query.getValueById(ctx._id)
  }
}

```

But there is no HowTo add the value by query using the `document._id`. Is that posible?

In case, that is not posible, how to realize this task?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [August 2, 2021, 7:29am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192/2 "2021-08-02T07:29:14Z")

</div>

It does not sound like update by query is the right tool. You probably need to write an external script that does this.

---

<div class="post-metadata">

**Author:** ![Rammi22](https://avatars.discourse-cdn.com/v4/letter/r/258eb7/32.png) [@Rammi22](https://discuss.elastic.co/u/Rammi22)\
**Post date:** [August 2, 2021, 9:22am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192/3 "2021-08-02T09:22:20Z")

</div>

Any other or maybe extended suggestion?

At the moment i can add dynamically a value by a method, but i can't pass a specific value of the current document as parameter:

```auto
// PHP Script
...
'{
    "script": {
        "inline": "ctx._source.myField=' 
          . $this->getRef( 'ctx._source.id' /*how to pass the parameter? */) . '",
        "lang": "painless"
    }
}'
...
private function getRef($id) 
{
    var_dump($id); // => return 'ctx._source.id' instaead the field value
    ...
}

```

---

<div class="post-metadata">

**Author:** ![Rammi22](https://avatars.discourse-cdn.com/v4/letter/r/258eb7/32.png) [@Rammi22](https://discuss.elastic.co/u/Rammi22)\
**Post date:** [August 2, 2021, 11:38am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192/4 "2021-08-02T11:38:25Z")

</div>

Come on guys... any hints?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [August 2, 2021, 11:59am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192/5 "2021-08-02T11:59:44Z")

</div>

Please be patient in waiting for responses to your question and refrain from pinging multiple times asking for a response or opening multiple topics for the same question. This is a community forum, it may take time for someone to reply to your question. For more information please refer to the [Community Code of Conduct](https://www.elastic.co/community/codeofconduct) specifically the section "Be patient". Also, please refrain from pinging folks directly, this is a forum and anyone that participates might be able to assist you.

If you are in need of a service with an SLA that covers response times for questions then you may want to consider talking to us about a [subscription](https://www.elastic.co/subscriptions).

It's fine to answer on your own thread after 2 or 3 days (not including weekends) if you don't have an answer.

---

<div class="post-metadata">

**Author:** ![Rammi22](https://avatars.discourse-cdn.com/v4/letter/r/258eb7/32.png) [@Rammi22](https://discuss.elastic.co/u/Rammi22)\
**Post date:** [August 6, 2021, 11:14am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192/6 "2021-08-06T11:14:17Z")

</div>

Ok, finally i found [a solution](https://www.elastic.co/guide/en/elasticsearch/client/php-api/current/search_operations.html#_scrolling):

```auto
$client = ClientBuilder::create()->build();
$params = [
    'scroll' => '30s',  
    'size' => 1,      
    'index' => 'my_index',
    'body' => [
        'query' => [
            'match_all' => new \stdClass()
        ]
    ]
];

$response = $client->search($params);

while (isset($response['hits']['hits']) && count($response['hits']['hits']) > 0) {

    // **
    // here i use the update-Api to add a new field with the value from the database
    // **

    $scroll_id = $response['_scroll_id'];

    $response = $client->scroll([
        'body' => [
            'scroll_id' => $scroll_id,
            'scroll' => '30s' 
        ]
    ]);
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 3, 2021, 11:14am UTC](https://discuss.elastic.co/t/update-by-query-for-each-field-with-value-by-sql-query/280192/7 "2021-09-03T11:14:34Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.