CODEWITHSUNDEEP
sqlite
JK36
JK36

Reputation: 853

Update column with random values within a range

I have a sqlite database and I'm populating sample data into my tables. The sample data needs a little messaging if the related data is going work properly, i.e. I need to start putting in primary and foreign keys in my tables.

First question is, how do I update the projectID column in my Project table for all the rows so that each row number is the value stored in the projectID field. I want this to be sequential and update the index.

update "project" set "projectID"= Count(*)

Second question is, I have to also update the customerID foreign key field in the project table. The only problem with this is, I want it to update between values 1 through to 50 and I want the rows to be updated randomly. Is this possible with sql, or am I going to have to export the data out of sqlite and process in a third party application and reimport.

Many thanks

Upvotes: 0

Views: 1639

Answers (1)

CL.
CL.

Reputation: 180080

  1. To get the row number, use the rowid:

    UPDATE project SET projectID = rowid;

  2. The documentation says:

    The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

    So to get values between 1 and 50, scale and offset these values appropriately:

    UPDATE project SET customerID = CAST(abs(random()) / 184467440737095517 AS INTEGER) + 1;

Upvotes: 3

Related Questions