Inspiring

Question

Updating db records with createUUID() with CFLOOP

Forum|Forum|15 years ago
February 14, 2010
3 replies
1208 views

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?

Advanced techniques

This topic has been closed for replies.

B

BuddWright

Participating Frequently

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__-

Inspiring

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.

S

sbudlongAuthor

Inspiring

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!

-

-__cfSearching__-

Inspiring

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

A

Adam Cameron.

Inspiring

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

D

Dan_Bracuk

Inspiring

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded