If you expect to do this more than once, you should use placeholders.  See
below.

I recommend you not use $a and $b outside the comparison block for sort(),
they cause the Perl compiler stage to act in special ways throughout the
code if they are found anywhere.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Tommy Wareing" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, March 30, 2001 2:16 AM
Subject: Re: Changing Multiple Rows Atomically


> On Fri, Mar 30, 2001 at 09:49:32AM +0200, Mark Vandenbroeck wrote:
> > Your solution IS atomic if your database supports transactions and read
> > consistency. This is the case with Oracle, but I don't know about the
others.
>
> That's really the important information, because there may well be
> operations you cannot perform in a single statement, but still want to
> be atomic.
>
> However, in this case...
>
> > John Passaniti wrote:
> > >         # set the first one to a temporary value
> > >
> > >         UPDATE report
> > >         SET order = 0
> > >         WHERE page = 2 AND order = 1
> > >
> > >         # set the second one to the first order value
> > >
> > >         UPDATE report
> > >         SET order = 1
> > >         WHERE page = 2 AND order = 2
> > >
> > >         # set the first one to the second order value
> > >
> > >         UPDATE report
> > >         SET order = 2
> > >         WHERE page = 2 AND order = 0
>
> UPDATE report
> SET order=DECODE(order, 1, 2, 2, 1)
> WHERE order IN (1, 2)
>
> (on the assumption you've got DECODE available: I have to admit I
> don't know if it's an Oracle-ism).
>
> Or, more generally:
> UPDATE report
> SET order=DECODE(order, $a, $b, $b, $a)
> WHERE order in ($a, $b)

$dbh -> {RaiseErrors} = 1;
my $sth = $dbh -> prepare( <<HERE );
UPDATE report
   SET order = DECODE( order, ?, ?, ?, ? )
   WHERE order in ( ?, ? )
HERE

while ( 1 ) {
   # doing something
   $sth -> execute( $first, $second, $second, $first, $first, $second );
   # doing something more
}


Reply via email to