Updating records in order (into an order)

[A Ratcliffe]

I'm storing questions for a user-defined quiz. I can store them in the order
they are entered without any problem. But...

The user needs to have the ability to re-order the questions if they wish,
or insert a question etc...

Currently, for the re-ordering, I'm either having to postback for every
re-order movement (I receive the Question code, the direction (Up/Down the
list) and then grab the Question and the on before/after it, and switch the
assigned question numbers (If I move question 5 to wards the start, I'd grab
the questionss in the quiz with field Position = (4,5) and set 4's position
to 5 and vice-versa. This is very slow (and could probably be done
better...ideas please)

To remedy the numerous postbacks, I'm working in a way to build a client
side array which will store the re-ordering, and then the user can submit it
all (I convert the array into a string I can parse server-side back into the
array). I end up with an array with:-

Question Identifier (guid)
Question position (int)(0 <-> (quizlength - 1))

Now, I could do an UPDATE on each QuestionID, and change the position each
time....which could take ages, and means sending a SQL UPDATE for each
question, or...

Is there any way I could do a block update where updates each question (by
identifier) with the new position as a single SQL command?

Hope I've explained the porblem well enough,

Thanks in advance for your help,

Ann-Marie Ratcliffe

[A Ratcliffe]

Oops...forgot to say...

I'm using Access as the backend DB....but I'd appreciate knowing how to do
it for both Access and SQL Server(if different).

Thanks again in advance,

Ann-Marie Ratcliffe

[Chris Hohmann]

"A Ratcliffe" <aratcliffe@archiNOSPAMmagic.net> wrote in message
news:%233cs6qYmDHA.1948@TK2MSFTNGP12.phx.gbl...

> I'm storing questions for a user-defined quiz. I can store them in the

order

> they are entered without any problem. But...
>
> The user needs to have the ability to re-order the questions if they

wish,

> or insert a question etc...
>
> Currently, for the re-ordering, I'm either having to postback for

every

> re-order movement (I receive the Question code, the direction (Up/Down

the

> list) and then grab the Question and the on before/after it, and

switch the

> assigned question numbers (If I move question 5 to wards the start,

I'd grab

> the questionss in the quiz with field Position = (4,5) and set 4's

position

> to 5 and vice-versa. This is very slow (and could probably be done
> better...ideas please)
>
> To remedy the numerous postbacks, I'm working in a way to build a

client

> side array which will store the re-ordering, and then the user can

submit it

> all (I convert the array into a string I can parse server-side back

into the

> array). I end up with an array with:-
>
> Question Identifier (guid)
> Question position (int)(0 <-> (quizlength - 1))
>
> Now, I could do an UPDATE on each QuestionID, and change the position

each

> time....which could take ages, and means sending a SQL UPDATE for each
> question, or...
>
> Is there any way I could do a block update where updates each question

(by

> identifier) with the new position as a single SQL command?
>
> Hope I've explained the porblem well enough,
>
> Thanks in advance for your help,
>
> Ann-Marie Ratcliffe

{Access}
1. Create a utility table called DUAL
2. Add a yes/no field to the DUAL table and also name it DUAL
3. Populate the table with one and only one record. The value doesn't
matter.
4. Construct a query similar to the one below for batch updates:

UPDATE
Q
SET
Q.QuestionPos = T.QuestionPos
FROM
tblQuestion AS Q,
[
SELECT 1 AS QuestionID, 3 AS QuestionPos FROM DUAL UNION ALL
SELECT 2, 4 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 5, 2 FROM DUAL
]. AS T
WHERE
Q.QuestionID = T.QuestionID

{SQL Server}
UPDATE
Q
SET
Q.QuestionPos = T.QuestionPos
FROM
tblQuestion AS Q,
(
SELECT 1 AS QuestionID, 3 AS QuestionPos UNION ALL
SELECT 2, 4 UNION ALL
SELECT 3, 5 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 2
) AS T
WHERE
Q.QuestionID = T.QuestionID

You may also be interested in the IMS Global Learning Consortium
Question And Test Interoperability (QTI) specification. Here's a link:
[url]http://www.imsglobal.org/question[/url]

HTH
-Chris Hohmann

[Aaron Bertrand - MVP]

> Now, I could do an UPDATE on each QuestionID, and change the position each

> time....which could take ages, and means sending a SQL UPDATE for each
> question, or...

Here's an example that might help. This essentially swaps the ranks of the
question chosen with the adjacent question (the one with the higher rank or
lower rank, depending on the @dir parameter). Another option would be to
explicitly set the rank...


CREATE TABLE questions
(
QuestionID INT IDENTITY(1,1),
Question VARCHAR(32),
Rank INT
)

SET NOCOUNT ON
INSERT questions(Question, rank) VALUES('who?', 1)
INSERT questions(Question, rank) VALUES('what?', 2)
INSERT questions(Question, rank) VALUES('where?', 3)
INSERT questions(Question, rank) VALUES('when?', 4)
INSERT questions(Question, rank) VALUES('why?', 5)
INSERT questions(Question, rank) VALUES('how?', 6)
GO

CREATE PROCEDURE dbo.shiftQuestion
@id INT,
@dir CHAR(1)
AS
BEGIN
SET NOCOUNT ON

DECLARE @f1 INT, @f2 INT
SET @f1 = CASE WHEN @dir = '+' THEN 1 ELSE -1 END
SET @f2 = -@f1

DECLARE @targetID INT
SELECT @targetID = questionID FROM Questions WHERE rank =
(SELECT rank FROM questions WHERE questionID = @iD) + @f1

UPDATE questions SET rank = CASE
WHEN QuestionID = @id THEN rank + @f1
WHEN QuestionID = @targetID THEN rank + @f2
ELSE rank END
END
GO

-- Now, try it out:

SELECT * FROM Questions ORDER BY rank

EXEC dbo.shiftQuestion 2, '+'

SELECT * FROM Questions ORDER BY rank

EXEC dbo.shiftQuestion 4, '-'
EXEC dbo.shiftQuestion 3, '+'

SELECT * FROM Questions ORDER BY rank
GO

DROP PROCEDURE dbo.shiftQuestion
DROP TABLE questions
GO

[Aaron Bertrand - MVP]

That's for SQL Server, by the way.


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:OWFpPoZmDHA.1728@TK2MSFTNGP09.phx.gbl...

> > Now, I could do an UPDATE on each QuestionID, and change the position

each

> > time....which could take ages, and means sending a SQL UPDATE for each
> > question, or...

>
> Here's an example that might help. This essentially swaps the ranks of

the

> question chosen with the adjacent question (the one with the higher rank

or

> lower rank, depending on the @dir parameter). Another option would be to
> explicitly set the rank...
>
>
> CREATE TABLE questions
> (
> QuestionID INT IDENTITY(1,1),
> Question VARCHAR(32),
> Rank INT
> )
>
> SET NOCOUNT ON
> INSERT questions(Question, rank) VALUES('who?', 1)
> INSERT questions(Question, rank) VALUES('what?', 2)
> INSERT questions(Question, rank) VALUES('where?', 3)
> INSERT questions(Question, rank) VALUES('when?', 4)
> INSERT questions(Question, rank) VALUES('why?', 5)
> INSERT questions(Question, rank) VALUES('how?', 6)
> GO
>
> CREATE PROCEDURE dbo.shiftQuestion
> @id INT,
> @dir CHAR(1)
> AS
> BEGIN
> SET NOCOUNT ON
>
> DECLARE @f1 INT, @f2 INT
> SET @f1 = CASE WHEN @dir = '+' THEN 1 ELSE -1 END
> SET @f2 = -@f1
>
> DECLARE @targetID INT
> SELECT @targetID = questionID FROM Questions WHERE rank =
> (SELECT rank FROM questions WHERE questionID = @iD) + @f1
>
> UPDATE questions SET rank = CASE
> WHEN QuestionID = @id THEN rank + @f1
> WHEN QuestionID = @targetID THEN rank + @f2
> ELSE rank END
> END
> GO
>
> -- Now, try it out:
>
> SELECT * FROM Questions ORDER BY rank
>
> EXEC dbo.shiftQuestion 2, '+'
>
> SELECT * FROM Questions ORDER BY rank
>
> EXEC dbo.shiftQuestion 4, '-'
> EXEC dbo.shiftQuestion 3, '+'
>
> SELECT * FROM Questions ORDER BY rank
> GO
>
> DROP PROCEDURE dbo.shiftQuestion
> DROP TABLE questions
> GO
>
>

[A Ratcliffe]

I spotted it was SQL Server ;). A very nice SP though. I'll see if I can
apply the logic to Access from my ASP page somehow.

Thanks,

Ann-Marie

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:uXqXVqZmDHA.3288@tk2msftngp13.phx.gbl...

> That's for SQL Server, by the way.
>
>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:OWFpPoZmDHA.1728@TK2MSFTNGP09.phx.gbl...

> > > Now, I could do an UPDATE on each QuestionID, and change the position

> each

> > > time....which could take ages, and means sending a SQL UPDATE for each
> > > question, or...

> >
> > Here's an example that might help. This essentially swaps the ranks of

> the

> > question chosen with the adjacent question (the one with the higher rank

> or

> > lower rank, depending on the @dir parameter). Another option would be

to

> > explicitly set the rank...
> >
> >
> > CREATE TABLE questions
> > (
> > QuestionID INT IDENTITY(1,1),
> > Question VARCHAR(32),
> > Rank INT
> > )
> >
> > SET NOCOUNT ON
> > INSERT questions(Question, rank) VALUES('who?', 1)
> > INSERT questions(Question, rank) VALUES('what?', 2)
> > INSERT questions(Question, rank) VALUES('where?', 3)
> > INSERT questions(Question, rank) VALUES('when?', 4)
> > INSERT questions(Question, rank) VALUES('why?', 5)
> > INSERT questions(Question, rank) VALUES('how?', 6)
> > GO
> >
> > CREATE PROCEDURE dbo.shiftQuestion
> > @id INT,
> > @dir CHAR(1)
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > DECLARE @f1 INT, @f2 INT
> > SET @f1 = CASE WHEN @dir = '+' THEN 1 ELSE -1 END
> > SET @f2 = -@f1
> >
> > DECLARE @targetID INT
> > SELECT @targetID = questionID FROM Questions WHERE rank =
> > (SELECT rank FROM questions WHERE questionID = @iD) + @f1
> >
> > UPDATE questions SET rank = CASE
> > WHEN QuestionID = @id THEN rank + @f1
> > WHEN QuestionID = @targetID THEN rank + @f2
> > ELSE rank END
> > END
> > GO
> >
> > -- Now, try it out:
> >
> > SELECT * FROM Questions ORDER BY rank
> >
> > EXEC dbo.shiftQuestion 2, '+'
> >
> > SELECT * FROM Questions ORDER BY rank
> >
> > EXEC dbo.shiftQuestion 4, '-'
> > EXEC dbo.shiftQuestion 3, '+'
> >
> > SELECT * FROM Questions ORDER BY rank
> > GO
> >
> > DROP PROCEDURE dbo.shiftQuestion
> > DROP TABLE questions
> > GO
> >
> >

>
>

[A Ratcliffe]

Hi, thanks for your reply. Could you break down what your code is doing?
Maybe its just me being slow-minded this morning, but I can't get my head
round what your system is doing, and I need to if I'm to build it into my
ASP page.

Thanks again,

Ann-Marie


"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:e53AvdZmDHA.2068@TK2MSFTNGP09.phx.gbl...

> "A Ratcliffe" <aratcliffe@archiNOSPAMmagic.net> wrote in message
> news:%233cs6qYmDHA.1948@TK2MSFTNGP12.phx.gbl...

> > I'm storing questions for a user-defined quiz. I can store them in the

> order

> > they are entered without any problem. But...
> >
> > The user needs to have the ability to re-order the questions if they

> wish,

> > or insert a question etc...
> >
> > Currently, for the re-ordering, I'm either having to postback for

> every

> > re-order movement (I receive the Question code, the direction (Up/Down

> the

> > list) and then grab the Question and the on before/after it, and

> switch the

> > assigned question numbers (If I move question 5 to wards the start,

> I'd grab

> > the questionss in the quiz with field Position = (4,5) and set 4's

> position

> > to 5 and vice-versa. This is very slow (and could probably be done
> > better...ideas please)
> >
> > To remedy the numerous postbacks, I'm working in a way to build a

> client

> > side array which will store the re-ordering, and then the user can

> submit it

> > all (I convert the array into a string I can parse server-side back

> into the

> > array). I end up with an array with:-
> >
> > Question Identifier (guid)
> > Question position (int)(0 <-> (quizlength - 1))
> >
> > Now, I could do an UPDATE on each QuestionID, and change the position

> each

> > time....which could take ages, and means sending a SQL UPDATE for each
> > question, or...
> >
> > Is there any way I could do a block update where updates each question

> (by

> > identifier) with the new position as a single SQL command?
> >
> > Hope I've explained the porblem well enough,
> >
> > Thanks in advance for your help,
> >
> > Ann-Marie Ratcliffe

>
> {Access}
> 1. Create a utility table called DUAL
> 2. Add a yes/no field to the DUAL table and also name it DUAL
> 3. Populate the table with one and only one record. The value doesn't
> matter.
> 4. Construct a query similar to the one below for batch updates:
>
> UPDATE
> Q
> SET
> Q.QuestionPos = T.QuestionPos
> FROM
> tblQuestion AS Q,
> [
> SELECT 1 AS QuestionID, 3 AS QuestionPos FROM DUAL UNION ALL
> SELECT 2, 4 FROM DUAL UNION ALL
> SELECT 3, 5 FROM DUAL UNION ALL
> SELECT 4, 1 FROM DUAL UNION ALL
> SELECT 5, 2 FROM DUAL
> ]. AS T
> WHERE
> Q.QuestionID = T.QuestionID
>
> {SQL Server}
> UPDATE
> Q
> SET
> Q.QuestionPos = T.QuestionPos
> FROM
> tblQuestion AS Q,
> (
> SELECT 1 AS QuestionID, 3 AS QuestionPos UNION ALL
> SELECT 2, 4 UNION ALL
> SELECT 3, 5 UNION ALL
> SELECT 4, 1 UNION ALL
> SELECT 5, 2
> ) AS T
> WHERE
> Q.QuestionID = T.QuestionID
>
> You may also be interested in the IMS Global Learning Consortium
> Question And Test Interoperability (QTI) specification. Here's a link:
> [url]http://www.imsglobal.org/question[/url]
>
> HTH
> -Chris Hohmann
>
>
>

[Chris Hohmann]

"A Ratcliffe" <aratcliffe@archiNOSPAMmagic.net> wrote in message
news:%23dBah5gmDHA.3316@TK2MSFTNGP11.phx.gbl...

> Hi, thanks for your reply. Could you break down what your code is

doing?

> Maybe its just me being slow-minded this morning, but I can't get my

head

> round what your system is doing, and I need to if I'm to build it into

my

> ASP page.

Sure. Before doing so, let me explain two somewhat obscure concepts:

1. The DUAL table:
The DUAL table is a carry-over from my experience with Oracle databases.
In Oracle, there is a system table called DUAL which, as I described is
a table with one field containing only one row of data. This is useful
for evaluating single expressions, testing functions, etc... For
example:

SELECT 2 + 2 AS SimpleMath FROM DUAL

The point here is that we don't care what's in DUAL, so long as we can
depend on the fact that there is one and only one record. That's what
allows us to retrieve a single, non-table based value. Having said that,
we can use the DUAL table in a UNION query to produce an arbitrary set
of data of our own choosing.

2. Undocumented Subqueries in MS Access 97
You did not indicate what version of Access you were using so I coded
for the least common denominator. The code I provided should work in
both Access2K and Access97. You may know that Access2K supports
subqueries, i.e. queries within queries, however, Access97 does not, or
does it?.....

Access97 does in fact have undocumented support for subqueries.
Specifically, the syntax for embedding a subquery into a query is to
wrap the subquery in square-brackets, followed by a period. Note, the
period is important an can be easily missed.

OK, so here's the breakdown (I put my comments in curly braces):

{Start of the UPDATE statement, indicating which table is to be updated.
Namely, the tblQuestion table which is aliased as Q}
UPDATE
Q

{The SET clause, indicating the field/value pair to update. Here, we are
setting the QuestionPos field in tblQuestion equal the value of the
QuestionPos field in the temporary table we create below that is aliased
as T}
SET
Q.QuestionPos = T.QuestionPos

{This is were we alias tblQuestion as Q}
FROM
tblQuestion AS Q,

{Here's where the magic happens. This is our embedded subquery. We
unioned together a list of the QuestionID's along with their new
QuestionPos values. We then alias the temporary subquery as T.}
[
SELECT 1 AS QuestionID, 3 AS QuestionPos FROM DUAL UNION ALL
SELECT 2, 4 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 5, 2 FROM DUAL
]. AS T

{The WHERE clause to associate each record in the tblQuestion table with
its associated record in the temporary subquery. We could have just as
easily used a JOIN clause.}
WHERE
Q.QuestionID = T.QuestionID


By way of analogy, the above query accomplishes the following is one
easy step:
1. Create a table called NewQuestionPosValues
2. Populate the new table with a separate INSERT statement for each row.
3. Create an UPDATE query that updates the tblQuestionPos with its
associated value in NewQuestionPosValues.
4. Delete the NewQuestionPosValues table.


HTH
-Chris Hohmann