Help with SQL query (add 5%)

[Mestika]

Hi everyone,

I’m having some difficulties with a query which purpose is to give users with more than one thread (called CS) in current year a 5% point “raise”. My relational schema looks like this:

Thread = (threadid, threadname, threadLocation)
threadoffering = (threadid, season, year, user)
user = (name, points)

Then, what I need is to check:

WHERE thread.threadid = threadoffering.threadid AND where threadoffering.year AND threadoffering.season = currentDate AND where threadoffering.User > 1 AND threadLocation = CS

then GIVE 5 % raise TO user.points

I hope it is explained thoroughly but otherwise here it is in short text:

Give a 5 % “point raise” to all users who has more than 1 thread in threadLocation CS in the current year and season (always dynamic, so for example now is year = 2010 and season is = spring).

I am looking forward to your answer

Sincerely,
Mestika

[johny_d]

UPDATE user AS u, thread AS t, threadoffering AS to
SET u.points = u.points * 1.05
WHERE t.threadid = to.threadid 
AND to.year = 2010
AND to.season = 'spring' 
AND t.threadLocation = CS
AND to.user = u.userid
AND u.userid > 1

You have to join somehow user table to threadoffering table (I supposed you have a userid field in user table that corresponds to user (or a userid) field in threadoffering table. If it's not, you should have it!
The rest of the query is pretty clear I think.
And the points field should be of type float or similar, so you can get decimals there (5% added to 3 point results in 3.15 points, which cannot be storred in an integer field)

[drjohn]

this

WHERE thread.threadid = threadoffering.threadid AND where threadoffering.year AND threadoffering.season = currentDate AND where threadoffering.User > 1 AND threadLocation = CS

has two WHEREs in it. Start by changing it to this

WHERE thread.threadid = threadoffering.threadid AND threadoffering.year AND threadoffering.season = currentDate AND where threadoffering.User > 1 AND threadLocation = CS

Then run just the query to check that it returns the correct data.

After that, the update can be considered.