2

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.

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!

Just add GUID field as AI .[1]

  • hello, thanks for your response, this did not solve my issue as i do have a primary key set to a different column. am looking for having GUID's uniquevalueper row is not a proper command – user88268 Feb 27 '16 at 15:39
  • Just for clarification, "UniqueValuePerRow" is not a function. What I meant was to use another column with a unique value (like your primary Key ) – JPorTer Feb 29 '16 at 17:12
0

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(...)...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.