0

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

3
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by drjohn
0
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)

Edited by johny_d: n/a

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.