FAQ
I'm a novice with the DBI and SQL. I'm hoping someone can either give me
some guidance here, or at least point me to some resource that can help.

Let's say I have a table called 'report' that looks like this:

page order text
1 1 'first thing'
1 2 'second thing'
1 3 'third thing'
2 1 'another thing'
2 2 'whatever thing'

The page column is used to select a group of rows that are presented to the
user as a page of data. The order column provides the sequence of data to
be shown within a page. So if I wanted to display the first page of data, I
could use SQL like this:

SELECT text
FROM report
WHERE id = 1
ORDER BY order

My user interface allows the user to change the order of items on a page by
shifting items up and down. Really, this is nothing more than swapping the
values for order. So for example, let's say I started with these two rows
in the database:

page order text
2 1 'another thing'
2 2 'whatever thing'

But I want to slide 'whatever thing' up one, producing these two rows:

page order text
2 1 'whatever thing'
2 2 'another thing'

So the question becomes, how do I do this? I want this swap to happen
atomically because other people may be looking at the database while I'm
making this change.

The best I can come up with are three SQL statements:

# 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

That does the swap, but it isn't atomic and I suspect not very efficient.
Is there some way to do all this in a single statement?

If it matters at all, the underlying databases I'm using are Microsoft
Access and MySQL.

Search Discussions

  • Mark Vandenbroeck at Mar 30, 2001 at 8:40 am
    John,

    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.

    Brgds,

    Mark


    John Passaniti wrote:
    I'm a novice with the DBI and SQL. I'm hoping someone can either give me
    some guidance here, or at least point me to some resource that can help.

    Let's say I have a table called 'report' that looks like this:

    page order text
    1 1 'first thing'
    1 2 'second thing'
    1 3 'third thing'
    2 1 'another thing'
    2 2 'whatever thing'

    The page column is used to select a group of rows that are presented to the
    user as a page of data. The order column provides the sequence of data to
    be shown within a page. So if I wanted to display the first page of data, I
    could use SQL like this:

    SELECT text
    FROM report
    WHERE id = 1
    ORDER BY order

    My user interface allows the user to change the order of items on a page by
    shifting items up and down. Really, this is nothing more than swapping the
    values for order. So for example, let's say I started with these two rows
    in the database:

    page order text
    2 1 'another thing'
    2 2 'whatever thing'

    But I want to slide 'whatever thing' up one, producing these two rows:

    page order text
    2 1 'whatever thing'
    2 2 'another thing'

    So the question becomes, how do I do this? I want this swap to happen
    atomically because other people may be looking at the database while I'm
    making this change.

    The best I can come up with are three SQL statements:

    # 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

    That does the swap, but it isn't atomic and I suspect not very efficient.
    Is there some way to do all this in a single statement?

    If it matters at all, the underlying databases I'm using are Microsoft
    Access and MySQL.
    --
    Mark Vandenbroeck Mobile : +32-495-59.55.62
    EMEA Support Information Systems Email : Mark.Vandenbroeck@oracle.com
    AIM : markvdb
  • Tommy Wareing at Mar 30, 2001 at 10:13 am

    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)

    --
    Tommy Wareing
  • Michael A. Chase at Mar 30, 2001 at 12:21 pm
    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" <twareing@oup.co.uk>
    To: <dbi-users@perl.org>
    Cc: <jpass@rochester.rr.com>
    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
    }

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedMar 30, '01 at 8:17a
activeMar 30, '01 at 12:21p
posts4
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase