Update query when all fields are optional

coldfusion, sql

Solution

If you're updating, and the argument was not passed then 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#

Problem

I have a table that I need to update where all columns are optionally passed to a method. I am then using ColdFusion to check if each column was passed and to add it to the update query. What is the best way to do this? I can't always update the user_id field because it is an identity field. Is there something similar to setting 1 = 1 like I have below that will work? The issue is just with the commas causing 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# ```

Original source