asked on

Update the same column with different values using 1 query.

Hello,

I just wondered if there was a way I could update two records in the same column with different values with the same query?

I have attached a code snippet to demonstrate what I mean.

At the moment I have two seperate queries updating two records. Is there a way perhaps two just doe this as 1 query instead of two?

Thanks in advance

Chris

<cfquery name="moveRecordDown" datasource="#dsl#">
	UPDATE menus SET
	
		sortedBy = #recordSort#
 
	WHERE id = (SELECT id FROM menus WHERE menuTypeID = #recordGroup# AND sortedBy = #previousRecordSort#)
</cfquery>
 
<cfquery name="moveRecordDown" datasource="#dsl#">
	UPDATE menus SET
	
		sortedBy = #previousRecordSort#
	
	WHERE id = #recordID#
</cfquery>

Open in new window

ASKER CERTIFIED SOLUTION

Plucka

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrissp26

ASKER

Brilliant! that helps a great deal. I take it the overhead of doing multiple statements in a single query is less than that of doing two seperate queries?

The only problem is the snippet you kindly attached doesn't seem to work. I get the following error:

Characters found after end of SQL statement.

If I remove the semi colon ; from the query qhich I assumed was causing the error then I get another error:

Syntax error (missing operator) in query expression 'id = (SELECT id FROM menus WHERE menuTypeID = 18 AND sortedBy = 8) UPDATE menus SET sortedBy = 8 WHERE id = 9'.

Thank you for your help and the very quick response

chrissp26

ASKER

Nevermind, I just did a search and found that you cannot run multiple statements in a single query using MS Access.

Thanks for your help

Chris

_agx_

> Is there a way perhaps two just doe this as 1 query instead of two?

Technicially it is possible. You could a CASE statement (or whatever Access's equivalent is). Use an IN (..) clause find only the two ID's you want. Then use CASE to update the value depending on the record ID.

--- omitted cfqueryparam for clarity
UPDATE YourTable
SET TheColumn = CASE WHEN ID = #TheFirstID# THEN #value1# ELSE #value2# END
WHERE ID IN (#TheFirstID#, #TheSecondID#)

Though I would probably just stick with the separate queries in this case.

chrissp26

ASKER

Cool. Thanks very much for the additional information.

Chris