Updating db records with createUUID() with CFLOOP

[sbudlong]

I have 7

00 subscribers in a db, and I've created a new column 'token' and I want to create a unique UUID for each row. Something like this, although this doesn't work. These are just tests on a test db with 15 records.

<cfquery datasource="maillist" name="createtoken">
 
    UPDATE subscribers
<cfloop from = "1" to = "15" index="token" step="1">
SET token =
<cfqueryparam cfsqltype="cf_sql_varchar" value="#createUUID()#">
</cfloop>

</cfquery>

OR

<cfloop from="1" to="15" step="1" index="i">
<cfquery datasource="maillist">
 
    UPDATE subscribers

SET token = <cfqueryparam cfsqltype="cf_sql_varchar" value="#createUUID()#">

</cfquery>
</cfloop>

I've been able to run the #createUUID()#" but it puts the same UUID in each row. Is is possible to UPDATE with unique UUIDs in every row?

[-__cfSearching__-]

UPDATE subscribers

SET token = <cfqueryparam cfsqltype="cf_sql_varchar"

value="#createUUID()#">

but it puts the same UUID in each row.

Without a WHERE clause filter, that UPDATE will modify every row in the table each time. If you wish to set a different value for each record, you need to update each record individually (ie By the unique recordID).

If it is a one time update of a small set of records, you could run a SELECT query to retrieve all record id's. Then cfloop through that query and run an UPDATE on each record ID ie (WHERE RecordID = #yourQuery.RecordID# ...)

[Adam Cameron.]

Over and above that, if possible, I'd probably put the GUID in using a native DB function (like newId() if one is using SQL Server), rather than do it with CF.

--

Adam

[Dan_Bracuk]

Over and above that, if you ever think that these uuid's will be part of a form field name, strip out the hyphens.

[sbudlong]

Thank you all for your help. You put me on the right track, and I solved my problem with CFLOOP. Couldn't have done it without your help!

[BuddWright]

As Adam stated, this is actually very simple, and you don't need to use CFLOOP.  With SQL Server, just do this:

<cfquery datasource="maillist">
    UPDATE subscribers

    SET token = NewID()

</cfquery>

With that, each of your subscribers now has a unique ID!

[-__cfSearching__-]

    UPDATE subscribers

    SET token = NewID()

If an MS SQL GUUID is preferred, yes it is easier. It works without a loop because of when the function is evaluated. CF functions are evaluated once, before the sql is sent to the database. The database function NewID() will obviously be evaluated in the database, ideally once per record. So unlike #createUUID()# its value can change.

On a side note, be careful with NewID(). It is not always evaluated once per record. In this case it almost certainly will be. But it is not guaranteed to behave that way all the time.