Presumably you are saving a trip to the db by doing it all in one cfquery; however the code would be much cleaner if you broke it into two cfqueries. Overall you'd be doing fewer SELECTS.
the first, getPage SELECT navlocation, sitelocation etc. FROM corppages where pageID = <cfqueryparam value="#ARGUMENTS.pageID#" cfsqltype="cf_sql_integer" /> the second, UPDATE SET whatever WHERE subpage = #getPage.subpage# AND navlocation = #getPage.navlocation# AND etc. If you're worried about race conditions then you could wrap the queries in CFTRANSACTION. -- Josh ----- Original Message ----- From: "Richard Cooper" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, November 06, 2006 2:20 PM Subject: optimise sql > hi all, > > just looking at this query and thought there must be a better way of > writing it. I'm probabley missing something obvious, here's the code: > > <cfquery name="editAllPages" datasource="#REQUEST.theDatabase#"> > UPDATE corpPages > SET priority = priority - 1 > WHERE active = '1' > AND subpage = (SELECT subpage FROM corppages pageID = <cfqueryparam > value="#ARGUMENTS.pageID#" cfsqltype="cf_sql_integer" />) > AND isFloat = '0' > AND navlocation = (SELECT navlocation FROM corppages pageID = > <cfqueryparam value="#ARGUMENTS.pageID#" cfsqltype="cf_sql_integer" />) > AND siteLocation = (SELECT sitelocation FROM corppages pageID = > <cfqueryparam value="#ARGUMENTS.pageID#" cfsqltype="cf_sql_integer" />) > AND priority > (SELECT priority FROM corppages pageID = <cfqueryparam > value="#ARGUMENTS.pageID#" cfsqltype="cf_sql_integer" />) > </cfquery> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259383 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
