# Update Values which are smaller than x **URL:** \ **Category:** InfluxDB 2\ **Created:** [February 27, 2021, 7:10am UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595 "2021-02-27T07:10:55Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![dominikbenner](https://avatars.discourse-cdn.com/v4/letter/d/d2c977/32.png) [@dominikbenner](https://community.influxdata.com/u/dominikbenner)\ **Post date:** [February 27, 2021, 7:10am UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/1 "2021-02-27T07:10:55Z")
Hi there, i am using Influxdb2 to store my smarthome values with openhab. Now with new OH version my energy measurements no longer stored as kWh but in Wh. So i have the old values and new values (=old value \*1000) mixed up in one measurement. How can i update the old values and recalculate them with value\*1000 ? thanks in advance br ---
**Author:** ![FixTestRepeat](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/fixtestrepeat/32/5208_2.png) [@FixTestRepeat](https://community.influxdata.com/u/FixTestRepeat)\ **Post date:** [February 28, 2021, 12:18am UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/2 "2021-02-28T00:18:52Z")
I feel your pain. In my opinion there needs to be more articles on how to handle this kind of thing. This is lifecycle / roadmap planning stuff and needs better info for users/admins to plan how they will handle this before they have two data formats collide. For now, (and maybe this is good enough forever in your environment) , you could probably to a conditional transform to normalize the values > **[Query using conditional logic in Flux | InfluxDB OSS 2.6 Documentation](https://docs.influxdata.com/influxdb/v2.0/query-data/flux/conditional-logic/#conditionally-transform-column-values-with-map)** > > This guide describes how to use Flux conditional expressions, such as if, else, and then, to query and transform data. Flux evaluates statements from left to right and stops evaluating once a condition matches. Eg (untested!) ``` from(bucket: "example-bucket") |> range(start: -5m) |> filter(fn: (r) => r._measurement == "mem" and r._field == "used_percent" ) |> map(fn: (r) => ({ r with r._value: if r._value >= 1000.1 then r._value / 1000 else r._value }) ) ``` ---
**Author:** ![dominikbenner](https://avatars.discourse-cdn.com/v4/letter/d/d2c977/32.png) [@dominikbenner](https://community.influxdata.com/u/dominikbenner)\ **Post date:** [March 7, 2021, 7:57am UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/3 "2021-03-07T07:57:30Z")
Hi! Thanks for your answer! But this is no solution for me. Meanwhile i tried to overwrite the wrong values (only 225) with a csv file. influx write imported it but the data did not change… Really frustrating! ---
**Author:** ![FixTestRepeat](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/fixtestrepeat/32/5208_2.png) [@FixTestRepeat](https://community.influxdata.com/u/FixTestRepeat)\ **Post date:** [March 7, 2021, 7:18pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/4 "2021-03-07T19:18:47Z")
What’s the retention on your bucket? If it’s not forever, then the two different magnitudes of data will be temporary. In which case, the normalization applied during any queries on that table should be sufficient, and will be redundant after the original magnitude data ages enough to be deleted. What error did the query throw when you ran it? Is there more to the issue ? ---
**Author:** ![dominikbenner](https://avatars.discourse-cdn.com/v4/letter/d/d2c977/32.png) [@dominikbenner](https://community.influxdata.com/u/dominikbenner)\ **Post date:** [March 7, 2021, 7:36pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/5 "2021-03-07T19:36:47Z")
It is forever… That is the point, the query returned no error. It ran successfully… So i have no idea why original data was not overwritten with new value. ---
**Author:** ![FixTestRepeat](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/fixtestrepeat/32/5208_2.png) [@FixTestRepeat](https://community.influxdata.com/u/FixTestRepeat)\ **Post date:** [March 7, 2021, 8:01pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/6 "2021-03-07T20:01:11Z")
Understand now. The query I put above only returns and transforms the data on the fly. Eg as part of a dashboard query. It doesn’t update the source data Perhaps @scott might know of a way to handle this. ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [March 8, 2021, 3:56pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/7 "2021-03-08T15:56:14Z")
@dominikbenner What was the query you used to overwrite the data with CSV? All data points in InfluxDB are uniquely identified by their timestamp, tag set, and field key. To overwrite an existing data point, provide an updated point with an identical timestamp, tag set, and field key and write that point back to InfluxDB. (I’ve added [influxdata/docs-v2#2256](https://github.com/influxdata/docs-v2/issues/2256) to start and track work for creating a guide that walks through overwriting data.) If you know the time and date that the data format changed, you should just be able to query data from before that time, update it, and write it back to InfluxDB: ```javascript dataChangeDate = 2021-01-01T00:00:00Z from(bucket: "example-bucket") |> range(start: -2y, stop: dataChangeDate) |> filter(fn: (r) => r._measurement == "example-measurement") |> map(fn: (r) => ({ r with _value: r._value * 1000 })) |> to(bucket: "example-bucket") ``` I’ve tested this method locally ☝ and it works. ---
**Author:** ![dominikbenner](https://avatars.discourse-cdn.com/v4/letter/d/d2c977/32.png) [@dominikbenner](https://community.influxdata.com/u/dominikbenner)\ **Post date:** [March 9, 2021, 7:27pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/8 "2021-03-09T19:27:30Z")
@scott Thanks for your answer! I tried with influx write with these test lines: ``` #datatype measurement,double,dateTime:RFC3339 Haushalt_Total,29601.526,2021-02-27T06:27:44.454Z Haushalt_Total,29601.5276,2021-02-27T06:27:50.261Z Haushalt_Total,29601.529,2021-02-27T06:28:01.724Z Haushalt_Total,29601.5304,2021-02-27T06:28:10.810Z Haushalt_Total,29601.5318,2021-02-27T06:28:21.061Z Haushalt_Total,29601.5332,2021-02-27T06:28:31.366Z Haushalt_Total,29601.5346,2021-02-27T06:28:41.669Z Haushalt_Total,29601.536,2021-02-27T06:28:52.070Z Haushalt_Total,29601.5372,2021-02-27T06:29:05.569Z Haushalt_Total,29601.5386,2021-02-27T06:29:12.179Z Haushalt_Total,29601.54,2021-02-27T06:29:22.230Z Haushalt_Total,29601.5414,2021-02-27T06:29:32.481Z Haushalt_Total,29601.5426,2021-02-27T06:29:42.527Z Haushalt_Total,29601.544,2021-02-27T06:29:52.576Z Haushalt_Total,29601.5454,2021-02-27T06:30:02.679Z Haushalt_Total,29601.5466,2021-02-27T06:30:12.731Z Haushalt_Total,29601.548,2021-02-27T06:30:22.783Z Haushalt_Total,29601.5494,2021-02-27T06:30:33.138Z Haushalt_Total,29601.5506,2021-02-27T06:30:43.180Z Haushalt_Total,29601.552,2021-02-27T06:30:53.228Z Haushalt_Total,29601.5532,2021-02-27T06:31:03.273Z Haushalt_Total,29601.5546,2021-02-27T06:31:13.323Z Haushalt_Total,29601.556,2021-02-27T06:31:23.373Z Haushalt_Total,29601.5574,2021-02-27T06:31:33.423Z Haushalt_Total,29601.5588,2021-02-27T06:31:43.581Z Haushalt_Total,29601.5602,2021-02-27T06:31:53.625Z Haushalt_Total,29601.562,2021-02-27T06:32:03.669Z Haushalt_Total,29601.5637,2021-02-27T06:32:13.868Z Haushalt_Total,29601.5649,2021-02-27T06:32:23.908Z Haushalt_Total,29601.5663,2021-02-27T06:32:33.956Z Haushalt_Total,29601.5677,2021-02-27T06:32:44.017Z Haushalt_Total,29601.5691,2021-02-27T06:32:54.216Z Haushalt_Total,29601.5703,2021-02-27T06:33:04.267Z Haushalt_Total,29601.5722,2021-02-27T06:33:14.315Z Haushalt_Total,29601.5737,2021-02-27T06:33:24.440Z Haushalt_Total,29601.5751,2021-02-27T06:33:34.486Z ``` There was no error but the data has not changed. I got the timestamps and values from my chronograf query.: `SELECT value FROM "openhab"."autogen"."Haushalt_Total" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND value > 1000000` I will try your flux query. br. ---
**Author:** ![dominikbenner](https://avatars.discourse-cdn.com/v4/letter/d/d2c977/32.png) [@dominikbenner](https://community.influxdata.com/u/dominikbenner)\ **Post date:** [March 9, 2021, 7:37pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/9 "2021-03-09T19:37:25Z")
> [@scott](#): > > `2021-01-01T00:00:00Z` Modified your query. It ran without error but the data did not change either… ``` from(bucket: "openhab") |> range(start: 2021-02-27T06:00:00Z, stop: 2021-02-27T09:00:00Z) |> filter(fn: (r) => r._measurement == "Haushalt_Total" and r._value > 100000) |> map(fn: (r) => ({ r with _value: r._value / 1000 })) |> to(bucket: "openhab") ``` ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [March 10, 2021, 4:23pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/10 "2021-03-10T16:23:55Z")
And you’re certain the following query returns data? ```javascript from(bucket: "openhab") |> range(start: 2021-02-27T06:00:00Z, stop: 2021-02-27T09:00:00Z) |> filter(fn: (r) => r._measurement == "Haushalt_Total" and r._value > 100000) ``` ---
**Author:** ![dominikbenner](https://avatars.discourse-cdn.com/v4/letter/d/d2c977/32.png) [@dominikbenner](https://community.influxdata.com/u/dominikbenner)\ **Post date:** [March 10, 2021, 6:16pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/11 "2021-03-10T18:16:03Z")
@scott mea culpa! Was my fault. It was the wrong bucket. Instead of “openhab” i had to use “openhab/autogen”. Now your script worked and my values are fine again. Thanks for your help! br ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [March 10, 2021, 8:00pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/12 "2021-03-10T20:00:55Z")
@dominikbenner Awesome! Happy to help! ---
**Author:** ![seema](https://avatars.discourse-cdn.com/v4/letter/s/7cd45c/32.png) [@seema](https://community.influxdata.com/u/seema)\ **Post date:** [June 24, 2022, 7:06am UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/13 "2022-06-24T07:06:29Z")
Hi Scott, It worked for me: from(bucket: “example-bucket”) |\> range(start: -2y, stop: dataChangeDate) |\> filter(fn: (r) =\> r.\_measurement == “example-measurement”) |\> map(fn: (r) =\> ({ r with \_value: r.\_value \* 1000 })) |\> to(bucket: “example-bucket”) i need to update the values for the host. For example → I need to update the values for a particular hostname. How to add where condition in this ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [August 4, 2022, 2:42pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/14 "2022-08-04T14:42:00Z")
@seema, you’d use `filter()` to filter the data to only rows with the hostname value you want to update. Then you use `set()` to update the set the value of the hostname column. ```plaintext from(bucket: “example-bucket”) |> range(start: -2y, stop: dataChangeDate) |> filter(fn: (r) => r._measurement == “example-measurement”) |> filter(fn: (r) => r.hostname == “old-hostname”) |> set(fn: (r) => ({key: "hostname", value: "new-hostname})) |> to(bucket: “example-bucket”) ``` One important thing to note here however is that because `hostname` is a tag (I’m assuming), when you write the updated data back to InfluxDB, it **will not** update the existing points. It will write them as new points. InfluxDB uniquely identifies points based on time, measurement, and tag set. Because the tag set would be different here, InfluxDB would see these as “new” data points. All the points with the old hostname would still exist. You’ll have to [manually delete those points](https://docs.influxdata.com/influxdb/latest/write-data/delete-data/). ---
**Author:** ![RedwoodRed](https://avatars.discourse-cdn.com/v4/letter/r/5fc32e/32.png) [@RedwoodRed](https://community.influxdata.com/u/RedwoodRed)\ **Post date:** [December 22, 2022, 6:38pm UTC](https://community.influxdata.com/t/update-values-which-are-smaller-than-x/18595/16 "2022-12-22T18:38:50Z")
Quick question on this: I’m using Chronograf 1.10.0 on influxDB 1.8 and I’m getting the error `error calling function "to": function "to" is not implemented`. [Here](https://community.influxdata.com/t/join-big-measurements-together-error-bad-gateway/15543/6) I read that Flux in InfluxDB 1.x is read-only, so I am not able to write data back to InfluxDB. Is this true? If yes, what’s the alternative? Using influxQL instead?