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#
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#
Well... you can count off the number of updates you do. That would "Fix" the query above. As in:
<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>
... you get the idea. Pretty messy though. I think I would be more likely to insure that the arguments are all there - passing in unchanged values so the query is updating whole dataset (or whatever).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With