cfquery problem with complex update statements

Question

Hello,

I am trying to fire Update Query using cfquery like below

   <cfquery name = "UpdateRecord"   
            dataSource = #DATASOURCE#   
            username = #DBUSER#   
            password = #DBPASSWORD# 
            result="updateResult" >  
        update table1 
set field1=( select field1 from table2 where field3='Some Value')
 where field4='someothervalue'
     </cfquery> 
    <cfdump var="#UpdateResult#">

But, when I execute this page, the page is not loading, in status bar I can see its loading for long time.

But If I use any simple Update Query like

update table1 set field1='abc' where field4='someothervalue'

then it is working fine

Can any one has idea how can I execute the queries like above using cfquery?

Thanks

Answer 1

Are you sure your subselect statement is returning 1 row. It depends on what RDMS you are running, but I'm pretty sure not all databases support this feature. I'd try running the query directly on your database first to see if it runs correctly.

You might be able to force sql to return only 1 row by putting a limit 1 on the end of your query, or if your database doesn't support it wrapping field1 in an aggregate.

SELECT MAX(field1) FROM table2 WHERE field3 = 'Some Value'

Note: If your String values are parameters from the user, you should make sure to use cfqueryparam to protect against SQL injection.

Answer 2

Did you try wrapping your update within PreserveSingleQuotes?

 <cfquery name = "UpdateRecord"   
        dataSource = #DATASOURCE#   
        username = #DBUSER#   
        password = #DBPASSWORD# 
        result="updateResult" >  
   #PreserveSingleQuotes(update table1 set field1=( select field1 from
   table2 where Field3='Some Value') where field4='someothervalue')#
 </cfquery>

Answer 3

If you can try using cfqueryparam for your values and you won't have to use PreserveSingleQuotes. It also protects against SQL injection.