Geek asks and answers

Refresh query if all fields are optional

I have a table that I need to update where all columns are optionally passed to the method.

Then I use ColdFusion to check if each column has passed and add it to the update request.

What is the best way to do this? I cannot always update the user_id field because it is an identification field. Is there something similar to setting 1 = 1, like mine below, that will work? The only problem is that commas cause syntax errors.

Thanks for any help.

update users
set 1 = 1
    <cfif len(arguments.userType)>,user_type = #arguments.userType#</cfif>
    <cfif len(arguments.primaryGroupId)>,primary_group_id = #arguments.primaryGroupId#</cfif>
    <cfif len(arguments.email)>,email = '#arguments.email#'</cfif>
    <cfif len(arguments.password)>,password = '#arguments.password#'</cfif>
    <cfif len(arguments.firstName)>,first_name = '#arguments.firstName#'</cfif>
    <cfif len(arguments.lastName)>,last_name = '#arguments.lastName#'</cfif>
    <cfif len(arguments.status)>,status = '#arguments.status#'</cfif>
    <cfif len(arguments.languageId)>,language_id = #arguments.languageId#</cfif>
    <cfif len(arguments.gmtOffset)>,gmt_offset = '#arguments.gmtOffset#'</cfif>
where user_id = #arguments.userId#
+5
source share
3 answers

If you are updating and the argument is not passed, just set it to the current value.

update users
set
    user_type = <cfif len(arguments.user_type)>#arguments.userType#<cfelse>user_type</cfif>
    ,primary_group_id = <cfif len(arguments.primaryGroupId)>#arguments.primaryGroupId#<cfelse>primary_group_id</cfif>
    ,email = <cfif len(arguments.email)>'#arguments.email#'<cfelse>email</cfif>
where user_id = #arguments.userId#
+4
source

... , . "" . :

<cfset updCt = false/>
<cfif len(arguments.usertype)>, user_type = #arguments.userType# 
    <cfset updCt = true/>
</cfif>
<cfif len(arguments.primaryGroupID)>
  <cfif updCt>,</cfif> 
    primary_group_id = #arguments.primaryGroupID# <cfset updCT = true/>
</cfif>

... . . , , - , ( - ).

+2

If you can make all of your arguments (other than UserID) optional and without a default value, you can do something like:

<cffunction name="updateUser">
 <cfargument name="userID" required="true">

 <cfset argumentMap = {field1 = {name="field_1", type="cf_sql_varchar"},
                      field2 = {name="field_2", type="cf_sql_numeric"},
                      ....} />
 <!--- where field1, field2, etc will match the name 
       of the arguments to your function--->

 <cfif arrayLen(structKeyArray(arguments)) gt 1>
   <cfquery>
   UPDATE users
     SET
      <cfloop collection="#arguments#" item="arg">
         #argumentMap[arg].name# = <cfqueryparam 
                                       value="#arguments[arg]#"
                                       type="#argumentMap[arg].type#" />
      </cfloop>
     WHERE user_id = <Cfqueryparam value="#arguments.userID#" type="cf_sql_numeric"/>
   </cfquery>
 </cfif>
</cffunction>

This will only execute the UPDATE statement with the given arguments.

0
source

More articles:


All Articles