update problems

[twcman]

I am having a problem updating a record field in the db. I have a field with a comma delimited list of id numbers. Is there a way to identify a record and remove a single id from the list in one statment. Similiar to this:

<cfquery name=&quot;recupdate&quot; datasource=&quot;#datasource#&quot;>
Update reports Set archived = '#archlistnew#',
hideview = ListDeleteAt(hideview,ListFindNoCase(hideview,userid,&quot;,&quot,&quot;,&quot
Where reportnumber= #findrecs.reportnumber#
</cfquery>

The text in red is where my problem lies. I get an error stating there is an undefined function. Not sure if sql supports that function or not, but that is what I need to accomplish.

Chris Scott
The Whole Computer Medical Systems

 

[jwdcfdeveloper]

Which type of db are you using? That will help, because there may be some functions for the given db that may help you out. If the variables you are passing are also in the db then maybe you can try a subquery for instance:

Update reports Set(archived) = (Select hideview From tableName Where userid = #userid#);

Works in Oracle, and should work in SQL Server, but I can't say for sure. Otherwise you can loop through the userids to find the appropriate userid and then plug that into your query. I hope that helps