# How manage insert and update sql (river)? **URL:** \ **Category:** Elasticsearch\ **Created:** [May 22, 2014, 9:43am UTC](https://discuss.elastic.co/t/how-manage-insert-and-update-sql-river/17671 "2014-05-22T09:43:24Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Tanguy\_Bernard](https://avatars.discourse-cdn.com/v4/letter/t/839c29/32.png) [@Tanguy\_Bernard](https://discuss.elastic.co/u/Tanguy_Bernard)\ **Post date:** [May 22, 2014, 9:43am UTC](https://discuss.elastic.co/t/how-manage-insert-and-update-sql-river/17671/1 "2014-05-22T09:43:24Z")
Hello, I would like to know a way to manage INSERT and UPDATE ? I am forced to delete and then re-index my data ? Maybe there are a way to index again but without duplicate my data (INSERT) ? Can you help with my problem ? I use this : PUT /\_river/user/\_meta { "type" : "jdbc", "jdbc" : { ``` "url" : "jdbc:mysql://my_adress/my_index", "user" : "my_user", "password" : "my_password", "sql" : "select name_user, firstname_user, id_user from user", "index" : "my_index", "type" : "user", "max_bulk_requests" : 5 ``` } } Thanks in advance. -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [May 22, 2014, 9:54am UTC](https://discuss.elastic.co/t/how-manage-insert-and-update-sql-river/17671/2 "2014-05-22T09:54:18Z")
If you use the column name \_id, you can control the ID of the ES document you created by SQL. If you do not use \_id, a random doc ID is generated. See the README at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) Jörg On Thu, May 22, 2014 at 11:43 AM, Tanguy Bernard \< [bernardtanguy1pro@gmail.com](mailto:bernardtanguy1pro@gmail.com)\> wrote: > Hello, > I would like to know a way to manage INSERT and UPDATE ? > I am forced to delete and then re-index my data ? > Maybe there are a way to index again but without duplicate my data > (INSERT) ? > Can you help with my problem ? > > I use this : > > PUT /\_river/user/\_meta > { > "type" : "jdbc", > "jdbc" : { > > ``` > "url" : "jdbc:mysql://my_adress/my_index", > "user" : "my_user", > "password" : "my_password", > "sql" : "select name_user, firstname_user, id_user from user", > "index" : "my_index", > "type" : "user", > "max_bulk_requests" : 5 > > ``` > > } > } > > Thanks in advance. > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoF%3DA0D%2BbMCz91sQ7e0ksQTx%2BgWe7wes0hf-We9enH7ByQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoF%3DA0D%2BbMCz91sQ7e0ksQTx%2BgWe7wes0hf-We9enH7ByQ%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Tanguy\_Bernard](https://avatars.discourse-cdn.com/v4/letter/t/839c29/32.png) [@Tanguy\_Bernard](https://discuss.elastic.co/u/Tanguy_Bernard)\ **Post date:** [May 23, 2014, 7:09am UTC](https://discuss.elastic.co/t/how-manage-insert-and-update-sql-river/17671/3 "2014-05-23T07:09:56Z")
Tank you Jörg, with \_id this is a perfect solution. Le jeudi 22 mai 2014 11:54:18 UTC+2, Jörg Prante a écrit : > If you use the column name \_id, you can control the ID of the ES document > you created by SQL. If you do not use \_id, a random doc ID is generated. > > See the README at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > Jörg > > On Thu, May 22, 2014 at 11:43 AM, Tanguy Bernard \<[bernardt...@gmail.com](mailto:bernardt...@gmail.com)\ > > > wrote: > > > Hello, > > I would like to know a way to manage INSERT and UPDATE ? > > I am forced to delete and then re-index my data ? > > Maybe there are a way to index again but without duplicate my data > > (INSERT) ? > > Can you help with my problem ? > > > > I use this : > > > > PUT /\_river/user/\_meta > > { > > "type" : "jdbc", > > "jdbc" : { > > > > ``` > > "url" : "jdbc:mysql://my_adress/my_index", > > "user" : "my_user", > > "password" : "my_password", > > "sql" : "select name_user, firstname_user, id_user from user", > > "index" : "my_index", > > "type" : "user", > > "max_bulk_requests" : 5 > > > > ``` > > > > } > > } > > > > Thanks in advance. > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/ac1201f4-38ac-4b9f-bdc5-582df667ba49%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ac1201f4-38ac-4b9f-bdc5-582df667ba49%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Ramy](https://avatars.discourse-cdn.com/v4/letter/r/8491ac/32.png) [@Ramy](https://discuss.elastic.co/u/Ramy)\ **Post date:** [November 17, 2014, 9:07am UTC](https://discuss.elastic.co/t/how-manage-insert-and-update-sql-river/17671/4 "2014-11-17T09:07:20Z")
Hi Jörg, I'm facing another problem with update! I'm looking for a solution for updating document if they have changes. When i run my query: curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "url" : "jdbc:mysql://xyz.com:3306/my\_test\_river\_es", "user" : "user\_name", "password" : "pass\_word", "schedule" : "0 0-59 0-23 ? \* \*", "sql" : "SELECT `id` AS \_id, `id` , `parent_id` , `name` FROM `account_navi`" } }' Everything is working fine. The river insert for me the new document to ES and it updates the documents every minutes. But I want to avoid, that ES execute the update for existing document with no changes. When I take a look at the ES. I see that river has updated all document. Is there any solution for my case? Thank you for any advice for the nice plugin Ramy Am Donnerstag, 22. Mai 2014 11:54:18 UTC+2 schrieb Jörg Prante: > If you use the column name \_id, you can control the ID of the ES document > you created by SQL. If you do not use \_id, a random doc ID is generated. > > See the README at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > Jörg > > On Thu, May 22, 2014 at 11:43 AM, Tanguy Bernard \<[bernardt...@gmail.com](mailto:bernardt...@gmail.com) > \\> wrote: > > > Hello, > > I would like to know a way to manage INSERT and UPDATE ? > > I am forced to delete and then re-index my data ? > > Maybe there are a way to index again but without duplicate my data > > (INSERT) ? > > Can you help with my problem ? > > > > I use this : > > > > PUT /\_river/user/\_meta > > { > > "type" : "jdbc", > > "jdbc" : { > > > > ``` > > "url" : "jdbc:mysql://my_adress/my_index", > > "user" : "my_user", > > "password" : "my_password", > > "sql" : "select name_user, firstname_user, id_user from user", > > "index" : "my_index", > > "type" : "user", > > "max_bulk_requests" : 5 > > > > ``` > > > > } > > } > > > > Thanks in advance. > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2ae75640-f6e4-4b1f-9216-b4e667e53171%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/02791b38-c359-48e2-9188-97b5f0f1f8a2%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/02791b38-c359-48e2-9188-97b5f0f1f8a2%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 12:49am UTC](https://discuss.elastic.co/t/how-manage-insert-and-update-sql-river/17671/5 "2017-07-06T00:49:38Z")