INSERT

[Andrew Sinning]

I've spent the last 2+ hours trying to build an update query in order to
repair a database which failed when the recursive methods which I was
previously using (inside an ASP script running on a server) timed-out
because the database got too big. Thank you so much for any help.

table: users
userId (integer)

table: quizzes
quizId (integer)
allowAll (boolean)

table: userQuizzes
quizId (integer, the join key for table "quizzes")
userId (integer, the join key for table "users")
enabled (boolean)
completed (boolean)

Here is what I want to do:

Given a variable "inputUserI d"

I need to create a new entry in table "userQuizze s"

FOR EVERY quizId in table "quizzes"

such that

userQuizzes.qui zId = quizzes.quizId
userQuizzes.use rId = inputUserId
userQuizzes.ena bled = quizzes.allowAl l
userQuizzes.com pleted = -1

BUT ONLY IF there isn't already an entry in userQuizzes WHERE

userQuizzes.qui zId = quizzes.quizId
AND userQuizzes.use rId = inputUserId
Here is what I CAN DO. The following UPDATE, will do the above FOR EVERY
userId in table "users" and EVERY quizId in table "quizzes". The problem is
that it takes too long, and the server times out.

01 INSERT INTO userQuizzes
02 SELECT
03 users.userId AS userId,
04 quizzes.quizId AS quizId,
05 allowAll AS enabled
06 -1 AS completed
07 FROM
08 (SELECT
09 users.userId,
10 quizzes.quizId,
11 allowAll
12 FROM users, quizzes
13 ) AS select_1
14 LEFT JOIN userQuizzes
15 ON select_1.userId = userQuizzes.use rId
16 WHERE userQuizzes.ena bled IS NULL

So, my next step would be to limit the above UPDATE to a specific userId,
i.e. WHERE users.userId = inputUserId

I can do this by adding the condition

WHERE users.userId = inputUserId

to either line 12 OR line 16.

It works just fine, but only if there are NO entries at all in the table
userQuizzes where userQuizzes.use rId = inputUserId.

If I run the querry with inputUserId = 1, then delete some of the entries
for userId = 1, when I run the querry again, there are no updates.

Please help me understant what is going wrong. I am stuck.

Thanks!