Update Query with function

[minli98]

Hi all,

Say, I have a database table with columns X and Y. Column X contains some strings, and I want to parse these strings and place the results in column Y.

So I created the udf parseFunction(StringInput), but how do I run the update query in ColdFusion?

When I tried the following:

<cfquery name="updateY" datasource="myDSN">
update myTable
set Y = '#parseFunction(X)#'
</cfquery>

CF gave me an error because it expects X to be a CF variable rather than a table column. What is the right syntax to run the above?

I know I can do this by doing 2 queries (a select and update), but it took a long time to run.

Any thought? Many thanks in advance.

Min

 

[r937]

if you write a CF function to do it, then you have to retrieve the data from the database before you can apply the function -- so this is your SELECT followed by UPDATE

much better would be a database function, not a CF function

r937.com | rudy.ca

 

[minli98]

Hi Rudy,

Thanks for the confirmation. I agree that doing it directly in db is much better, it's just my t-sql is a bit weak and it takes me longer to write a function in that platform.

Thanks!
Min