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