By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,101 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,101 IT Pros & Developers. It's quick & easy.

INSERT

P: n/a
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!

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.