Updating data in a column using it's own value

[drmaves]

I trying to change all the entries in a column of a table to titlecase. I have
a custom tag that does the case change but I can't get it to work in the
following query:

<cfquery name="UpdateRes" datasource="#application.dsn#">
UPDATE idx_res
SET city = <cf_changecase case="titlecase" string="#city#"
result="convCity">'#convCity#'
</cfquery>

I get a variable not found error for #city#. It must not recognize it because
it's coming from the same table.

Is there a way to work around this?

[adonis1976]

check if this works for u ...

<cfquery name="qGetNames" datasource="#application.dsn#">
select city from idx_res
</cfquery>
<cfloop query="qGetNames">
<cfquery name="UpdateRes" datasource="#application.dsn#">
UPDATE idx_res
SET city = <cf_changecase case="titlecase"
string="#qGetNames.city#" result="convCity">'#convCity#'
</cfquery>
</cfloop>

[drmaves]

It almost worked but not quite.

The script changed the case of the first record and then used that value for
all the records as the replacement.

Every record's city value is now the same.

Any idea on why each record wasn't processed idependently?

[Kronin555]

because you don't have a WHERE clause in your update query. It's doing exactly what you told it to do.

[drmaves]

Okay, that makes sense. I put a WHERE clause in my UPDATE query but it doesn't
recognize qGetNames.
I get the follow error message: Unknown table 'qGetNames' in where clause.
What do I need to do to get it to recognize it?

Here's where my code stands now:

<cfquery name="qGetNames" datasource="#application.dsn#">
SELECT city, listing_num
FROM idx_res
</cfquery>

<cfloop query="qGetNames">
<cfquery name="UpdateRes" datasource="#application.dsn#">
UPDATE idx_res
SET city = <cf_changecase case="titlecase" string="#qGetNames.city#"
result="convCity">'#convCity#'
WHERE idx_res.listing_num = qGetNames.listing_num
</cfquery>
</cfloop>

[adonis1976]

this is what u need:

<cfquery name="qGetNames" datasource="#application.dsn#">
SELECT city, listing_num
FROM idx_res
</cfquery>

<cfloop query="qGetNames">
<cfquery name="UpdateRes" datasource="#application.dsn#">
UPDATE idx_res
SET city = <cf_changecase case="titlecase" string="#qGetNames.city#"
result="convCity">'#convCity#'
WHERE idx_res.listing_num = #qGetNames.listing_num#
</cfquery>
</cfloop>

[shp.jc]

This would probably be a bit clearer for you and future developers if you put
the custom tag call outside the cfquery tag like this. Better yet, you could
write title case as a <cffunction> and then say <cfset convCity =
titlecase(qgetNames.city) />

Either way, if you're making a lot of updates, I'd use <cfqueryparam>

<cfloop query="qGetNames">
<cf_changecase case="titlecase" string="#qGetNames.city#"
result="convCity">
<--- or <cfset convCity = titleCase(qgetNames.city) /> --->
<cfquery name="UpdateRes" datasource="#application.dsn#">
UPDATE idx_res
SET city = <cfqueryparam value="#convCity#" cfsqltype="cf_sql_varchar" />
WHERE idx_res.listing_num = <cfqueryparam value="#qGetNames.listing_num#"
cfsqltype="cf_sql_integer" />

</cfquery>
</cfloop>