Batch Update in Access?

[hillsy]

Hi everyone,

I need to update two records in one query on an Access database (increment one value by 1, decrement the other by 1).

Any idea how to do this? I'm on Access 97 using CF Express for this one and so far I haven't been able to get it to work...

[Jeremy W.]

1. Select the 2
2. Run your increment/decrement routine
3. UPDATE with the new values

...

Or am I thinking it's simpler then it is?

[dhtmlgod]

Sunny, simpler:

SQL = "UPDATE tblsBlah SET value1 = value1 + 1, value2 = value2 - 1 WHERER blah = 'hoho'"

[hillsy]

Possibly - though I'm not entirely sure what you actually meant so it could just be me being dumb

OK...

What happens is I have a bunch of records like so:

tblForgetExactName
---------------------
ID | Text | ListOrder

tblWhatverTheNameIs.ListOrder is the crucial value here. It's used to define the sort order for records from this table, when they're output to a page.

Say I want to push a value up a list (thereby changing the output order). So I have a link (called "Move Up") that passes the record ID and ListOrder values to an action page.

On the action page, I need to have something that will decrement the ListOrder value of that record (remember it's moving up the list) and increment the value of the record above it - so the records essentially swap places in the sort order. At the moment I'm trying a vanilla update statement but it's not going too well.

Any ideas?

I'm not sure I like this approach either. So if anyone has any alternatives, bring 'em out

ColdFusion Express is preferable, but I may be able to swing ASP too....

[Jeremy W.]

D's code is essentially waht you need then

[hillsy]

Originally posted by dhtmlgod
Sunny, simpler:

SQL = "UPDATE tblsBlah SET value1 = value1 + 1, value2 = value2 - 1 WHERER blah = 'hoho'"

Ja, but won't that just update several fields in the same record - i.e. 'hoho'

I need to update the same field in two different records

<edit>
Could always run two update queries one after the other. But Access doesn't seem to like that...
</edit>

[Jeremy W.]

Ah, I see, I was wondering where the confusion was!

[hillsy]

Oi - I may be a bit dim, but really....

I don't have the application right in front of me unfortunately, so might post some SQL tomorrow for you to kick round

[kevin_tremblay]

hillsy post your sql and lets give it a look at, sounds to me this is an easy solution . I will check back and see if you posted code and give it a look at.

[hillsy]

OK...

What I have is table listing Product Groups like so:

tblProductGrp
--------------------------
GroupName | ListOrder | ID
--------------------------
Group1    | 1         | 1 
Group2    | 2         | 2 
Foobar    | 4         | 3 
Splon     | 3         | 5

These are output onto a page, ordered by ListOrder.

As part of the web-based admin for this, I need to be able to reorder the output (i.e. change the ListOrder values). Currently this is done by using "Move this value up" and "Move this value down" URLs which pass the following parameters:

Current ListOrder value = #URL.CurrentOrd#
ID = #URL.ID#
Action (move up or down) = #URL.Action#

The code to process this is (in part):

  <cfif URL.Action is "MoveUp">
  
    <!--- error catching --->
    <cfif URL.CurrentOrd is 1>
      <cfoutput>
        <p>You cannot move this value any further up the list</p>
        <p>Go <a href="sup_options.cfm">back</a></p>
      </cfoutput>
      <cfabort>
    </cfif>

    <!--- decrement the ListOrder by 1 --->
    <cfquery datasource="SuppInfo" dbtype="ODBC" name="MoveGrpUp">
     UPDATE tblProductGrp
     SET ListOrder = #URL.CurrentOrd#-1
     WHERE ID = #URL.ID#
    </cfquery>
  </cfif>

There is no problem with incrementing/decrementing the ListOrder as shown above. But what I want to be able to do is increment one and decrement the value immediately above it (or vice versa). So I need to pass an UPDATE that updates two records at once. Access seems to have problems with that (and doesn't like it if I try passing two sequential statements either - really funny things happen then).

Ideas?

[ownersite]

hillsy,

Why not just run two queries? Is there a particular reason why it has to be done with one?

[hillsy]

Access doesn't seem to like two. Not sure why. Will post the sympotms when I get a bit more time (just about to go home from work )

[nagrom]

Hey Hillsy,

I've done sequential updates in access without any trouble...

Just a thought, try sticking in a CFLOCATION after the first update, that points to a template that performs the second update. You shouldn't have to do this, but it sounds like the server is locking the table for some reason, and this might release it.

[hillsy]

Hmmmm - now that's an idea

I'll give it a go and let you know how I get on...

[ownersite]

Maybe I misstated what I meant...I didn't mean to place two queries inside a <CFQUERY> tag, rather to use multiple <CFQUERY> tags...

<CFQUERY>
update record 1
</CFQUERY>

<CFQUERY>
update record 2
</CFQUERY>

[nagrom]

hey hillsy, what happened?
We're curious.

[hillsy]

I'm @ work and it's on my list of things to do today

Will let you know.... <drumroll>

[Mytch2001]

Hi,
You should be able to execute one query after the other. I don't know much about CF, but cant you just set the query, execute it, and then do this again?

[hillsy]

For all those who were following this saga, it turned out to be embarrassingly simple

I just needed to swap round my sequential updates and change #URL.CurrentOrd#-1 to #URL.CurrentOrd#

I must have been tired not to pick that up sooner....

Here's the code anyways:

<!--- move the entry below the selected value one up the list order --->
<cfquery datasource="foo" dbtype="ODBC" name="MoveGrpUp">
    UPDATE tblProductGrp
    SET ListOrder = #URL.CurrentOrd#
    WHERE ListOrder = #URL.CurrentOrd#+1
</cfquery>

<!--- move the selected entry one down the list order --->
<cfquery datasource="foo" dbtype="ODBC" name="MoveGrpDown">
    UPDATE tblProductGrp
    SET ListOrder = #URL.CurrentOrd#+1
    WHERE ID = #URL.ID#
</cfquery>

You just swap a few things round to move the group the other way....