conditional 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 "inputUserId"

I need to create a new entry in table "userQuizzes"

FOR EVERY quizId in table "quizzes"

such that

userQuizzes.quizId = quizzes.quizId
userQuizzes.userId = inputUserId
userQuizzes.enabled = quizzes.allowAll
userQuizzes.completed = -1

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

userQuizzes.quizId = quizzes.quizId
AND userQuizzes.userId = 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.userId
16 WHERE userQuizzes.enabled 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.userId = 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!

 

[[MVP] S.Clark]

Do you have to do ALL of the steps ALL at once?

Consider that you can perform parts of the population in smaller pieces,
such that it's not such a burden on the server. You've got a cross product
within a subquery AND an outer join.

Maybe breakdown the steps and perform them one at time.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-