SQL Update Dynamic Columns

I think what everyone keeps missing is the fact that original poster is trying to create an update statement in which he has not idea what columns are going to be updated (which, in itself is a really bad design).  So, this update statement has to have the ability to update any, all, or no columns in the table. He be updating 1 column, or 100.

In reading back over the original post, it seems that what he's asking for is a way to have 1 constant variable always be updated so that he can easily dynamically determine whether or not to update any of the other columns.  The reasoning behind that is pretty simple, without the 1 constant variable, you've got to have a lot of conditional login in there to determine when and whether or not to use commas in the update statement.  With 1 constant variable in your update, you eliminate the need for all of the conditional logic.

In his design, it seems that the only constant he has is the primary key, which is an autonumber column, which is not allowed to be updated via SQL.  So, what do you do?  If you can't use the only constant that you have, you either have to create another constant to use or have a bunch of conditional logic in your SQL statement to try to figure things out.

I guess a better way to understand it would be to phrase it like this:  How would you write the below update statement to work when columns A, B, and C may or may not be available any time the statement is run, and the statement needs to run with any combination of the columns?

UPDATE myTable
SET  columnA = <cfqueryparam value="#arguments.columnA#">
          ,columnB = <cfqueryparam value="#arguments.columnB#">
          ,columnC = <cfqueryparam value="#arguments.columnC#">

Is there an easy way to write an sql UPDATE statement where the columns to be updated are not known in advance?  I am thinking of the equivalent of 0=0 in GET statement.  For example: SELECT * FROM myTable WHERE 0=0 <cfif structKeyExists( arguments, 'columnA' )>     and columnA = <cfqueryparam value="#arguments.columnA#"> </cfif>

That's a really awful way of handling this: adding unnecessary statements into your SQL just to make your life easier.  Although it's not as bad as the suggestion to add an entire exta column to the table that someone else made!

In a lot of situations the DB engine will see nonsense statements like WHERE 0=0 and factor them out, however I have seen other situations in which - for reasons I didn't investigate - the DB will still do an entire table scan if you have that sort of thing in your SQL.  Because, by default, a statement in a WHERE clause is run for every row in the resultset.

Instead, I'd do this:

<cfset sWhereAnd = "WHERE">

SELECT col

FROM myTable

<cfif structKeyExists(arguments, "columnA")>

#sWhereAnd# columnA = <cfqueryparam value="#arguments.columnA#">

<cfset sWhereAnd = "AND">

</cfif>

etc

That way one gets the SQL statement one actually wants.  And it will work in your UPDATE query too.

--

Adan

If you've got control over the DB columns, and don't mind adding a new column, the simplest thing would be to do something like this:

UPDATE myTable
SET lastUpdated = #Now()#
<cfif structKeyExists( arguments, 'columnA' )>
     , columnA = <cfqueryparam value="#arguments.columnA#">
</cfif>

Just create a LastUpdated column and pass in the current date/time to it.  Or you can create any dummy column that really serves no purpose other than for you to have a constant in your query.

Or, if you really want to get fancy with it:

<cfset variables.needsComma = false>
UPDATE myTable
SET lastUpdated = #Now()#
<cfif structKeyExists( arguments, 'columnA' )>
      <cfif variables.needsComma>,</cfif>columnA = <cfqueryparam value="#arguments.columnA#">
     <cfset variables.needsComma = true>
</cfif>
<cfif structKeyExists( arguments, 'columnB' )>
      <cfif variables.needsComma>,</cfif>columnB = <cfqueryparam value="#arguments.columnB#">
     <cfset variables.needsComma = true>
</cfif>

The alternative is to know what inputs you can possibly receive and what you want to do with them.

If you are looking for the equivalent of "where 0=0" in an update query, it's "set somefield = somefield".  The rest will be

, someotherfield = somevalue