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.

Reply via email to