TABLES:
- student(name,points)
- playsin(name->student(name), score)
I'm trying to use:
UPDATE STUDENT SET points = points + CONSTANT * (SELECT score FROM PLAYSIN WHERE playsin.name = student.name); 
- Could you please re-write it a little bit and make it more understandable?Filip Ekberg– Filip Ekberg2009-10-10 22:56:56 +00:00Commented Oct 10, 2009 at 22:56
- @Filip: The algorithm reads to me as multiplying the student's score by a constant before adding to the existing students' point value.OMG Ponies– OMG Ponies2009-10-10 23:02:33 +00:00Commented Oct 10, 2009 at 23:02
- This assumes that "name" can be used as a primary key, right?Thorsten– Thorsten2009-10-10 23:07:25 +00:00Commented Oct 10, 2009 at 23:07
- @IronGoofy: Safe bet if it's a foreign key in the PLAYSIN table.OMG Ponies– OMG Ponies2009-10-10 23:16:01 +00:00Commented Oct 10, 2009 at 23:16
- @rexem, just as i thought then. :)Filip Ekberg– Filip Ekberg2009-10-10 23:49:35 +00:00Commented Oct 10, 2009 at 23:49
Add a comment |
3 Answers
Try:
UPDATE STUDENT s SET points = s.points + CONSTANT * (SELECT p.score FROM PLAYSIN p WHERE p.name = s.name) Table aliases are a very good habit to have.
3 Comments
Thorsten
Can't try it out right now, but is the alias really required? It seems that the references are clear even without the aliases.
OMG Ponies
@IronGoofy: I can't test either :/ But it's either the fact of referencing the points value in the equation to update it, or the join to the PLAYSIN table I figure.
Link
Thanks, using aliases solved my problem. Thank you very much.
You might also try updating an in-line view:
UPDATE (select s.name student_name, s.points student_points, p.score playsin_score from STUDENT s, PLAYSIN p where p.name = s.name) SET student_points = student_points + CONSTANT * playsin_score; It also limits the rows in STUDENT that are updated to only the set for which there is a row in PLAYSIN (you might also consider in your current code what happens to SCORE if that subquery could return NULL). You'd need a unique or primary key on playsin.name to use this syntax to avoid a join cardinality check error, but if this is not feasible then a MERGE statement might be useful. A MERGE might be worth considering anyway if you also have code to add into STUDENT any new names in PLAYSIN.
Comments
You can try this next :
UPDATE student s, playsin p SET s.points = s.points + CONSTANT * p.score WHERE p.name=s.name 
