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  : [EMAIL PROTECTED]
                                       AIM    : markvdb


Reply via email to