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.