Update function to enter random/unique GUID per row

Question

I am new to SQL and am trying to do the following.

i am using SQLite browser to edit the current file I have

I do have a SQLite database with around 1500 rows of data that I would like to create a GUID for.

I created the Col with the name GUID and has a default value of NULL.

My update script looks like:

UPDATE "main"."MyLab" SET guid = (select hex( randomblob(4)) || '-' || hex( randomblob(2))
             || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
             || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
             substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6)) )

This gives the same value to all 1,500 rows. I want a unique value for each row.

Answer 1

Just Add another column to your table and mark the AI checkbox. (AI=Autoincrement). This will make it automatically a PK (Primary Key).

It will automatically populate the column with a different number in each row.

Now if you want to create another type of key, use this GUID field in your update as part of the calculation so that it will variate in each row.

Why the update didn't work?

The reason for why your update didn't work it b/c it sets all values the same since you didn't have a condition to your update.

For example:

Update 
    MyTable
set GUID=1
where
   AnotherColumn=1

In your update, you don't have a condition, hence all row values will be updated the same.

For example:

Update MyTable
set GUID=1+1

In other words, Update my table and set the GUID column with the value of 2.

it would be the same as having the following Update

Update MyTable
set GUID=1+1
where 
    1=1

The WHERE clause filters which rows it will update.

So in conclusion if you want to create a GUID, you would need a variant in the formula per row.

Update MyTable
set GUID=1+UniqueValuePerRow

Since an update will not help you achieve what you want, you might want to create a while loop to update 1 row at a time or learn about Cursors.

...or simply add an auto increment field like in the image below.

Cheers!

Answer 2

The subquery does not depend on any value from the MyLab table, so it needs to be evaluated only once.

However, this computation does not need to be a subquery. Just remove the parentheses and the SELECT:

UPDATE MyLab SET guid = hex(...)...