MySQL Forums
Forum List  »  Newbie

Re: subquery rewritten for 4.0
Posted by: Erin ONeill
Date: July 20, 2005 01:53PM

I think Mondays are not a good day to post something this convoluted! :)

I can figure out how to do this in perl or php but not in SQL! This is version 4.0.20 so NO SUBQUERIES.

Let me rephrase this:

I have a small lookup table. It's basically what I would call a hash table in perl. There's a key and there's a value. The key is an int, the value is text. something like this:

0==> newbie
5==>stranger
35==>"I'm testing the waters"
100==>"I'm a regular"

Then each time a user posts to the forums it's checked against this hash table (which the forums ppl did NOT create as a hash table!). Then in their User table they get the title associated with the key. So if I've posted 4 times, I'm still a newbie. Only when I get to 5 posts do I make it to stranger level.

Ok. This code works. EXCEPT when they upgraded the forums they changed the titles and now we've got to go thru the user table (HUGE) and change all the titles based on the value of tot_posts in their user table compared to the key in the hash tables of user titles.

So I've got some users who have 102 posts but their title is NOT "I'm a regular" but some other string and we want to change ALL the users (hundreds of thousands) to the new and improved correct titles.

I can do it in perl. It can be done in php I'm pretty sure. But I do think SQL could probably handle this faster and cleaner. And I do have this nagging suspcion that even MySQL 4.0 can handle this!

AND there is a subquery that is indeed "sort of working" in MySQL 4.1. It can update ALL the users but we really want to update ALL the users but those with titles like 'Club%' or 'Newsletter%'.

So this is a multi part quesion:

How to do the basic query in 4.0.20 without ANY SUBQUERIES?

How to do the fancy query with the above exceptions in both 4.0.20 AND 4.1.17??

And I'd love some pointers to places on the web to study how to improve my complex query writing. All the tutorials seem to be geared towards the beginner and not the intermediate.

Thanks!

erin

Options: ReplyQuote


Subject
Written By
Posted
July 18, 2005 05:23PM
Re: subquery rewritten for 4.0
July 20, 2005 01:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.