Need help adding random float in SQLite

Question

I am trying to add a random float to an existing value for an entire column in SQLite

Adding a number to an existing shouldn't be hard and UPDATE myTable SET Column1=Column1+1 should work. If I am correct it will get each value in column1 and add 1 to it.

The problem is getting the random number, my aim is to have something similar to this

UPDATE mytable SET Column1=(Column1 + (RAND Between 0.5 AND 0.8));

so each row has a different random float added that's been generated between 2 floats added to it.

My issue is that the only reference I have found for random under SQLite is that the random function generates an integer.

Answer 1

You can get a random floating point number in the interval (-1, 1] from SQLite like so:

SELECT RANDOM() / CAST(-9223372036854775808 AS REAL)

Therefore, you can get a value in the standard random number interval of [0, 1) with:

SELECT 0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2

...and modify as appropriate from there.

Answer 2

While the other answers here are equally valid ways of doing this, I should mention that SQLite has an API for calling foreign functions, which is exposed in the bindings for a number of languages. This could let you define a binding to your host language's random function instead of using the one that's built-in. In Python, for example, using their sqlite3 module, it's a simple as this :

import random, sqlite3
db_conn = sqlite3.connect(":memory:")

# A function named "random_between" with 2 arguments
# that calls the python function random.uniform
db_conn.create_function('random_between', 2, random.uniform)

results = db_conn.execute("SELECT random_between(0.3, 0.8)").fetchall()
print(results)

This avoids cluttering your SQL query with arithmetics and makes it look a lot more like what you were initially aiming for. It does require that you run the query via the C API or a language-specific binding to it, this cannot work as a "pure SQL" solution.