SQL Update Dynamic Columns

Report · Sep 28, 2010

Hi,

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>

The 0=0 does not work in UPDATE statement. I can use any column whose values I know will never change such as the primary key. However, it only works if the primary key is not auto-increment. Unfortunately, many of my tables use auto-increment primary key.

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

Is there a good alternative? Thank you.

Regards,

Report · Sep 28, 2010

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

Report · Sep 28, 2010

Hi Dan,

Thanks for the quick reply. The problem with your suggested solution is that 'somefield' has to be a column that will not get updated. In many of my tables, the only column that will not get updated is the primary key. However, using the primary key as the 'somefield' does not work if it is auto-increment.

Dan Bracuk wrote:
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

Report · Sep 29, 2010

Regarding: "However, using the primary key as the 'somefield' does not work if it is auto-increment."

Why not?

Report · Sep 29, 2010

Most databases will not allow you to update (or insert) an auto-id column.

Report · Sep 28, 2010

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>

Report · Sep 29, 2010

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:

SELECT col

FROM myTable

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

</cfif>

etc

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

--

Adan

Report · Sep 29, 2010

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#">

Report · Sep 29, 2010

One can argue over which solution is the more elegant or efficient, but both suggestions by ecobb and adam solved my question. So thank you both!

Report · Sep 29, 2010

Same as my previous example except instead of having sWhereAnd being intialised to "WHERE" and then subsequently changed to "AND", you have a variable sSeparator which initialises to "" and then is set to ",".

If the possible column names are known, and it's just which subset of those columns is not known, then the series of conditionals is basically unavoidable.

If there is no change from one conditional block to the next except for the column name - eg: the variable holding the value for the column is the same as the column name - then one can loop over a list of all possible columns and have the conditional implemented dynamically, eg:

#separator# #column# = <queryparam #variable[column]#>

</if>

</loop>

I think that's about as expedient as one can get.

--

Adam