How can I refer to the row that the update is currently updating?

Question

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);

Answer 1

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.

Answer 2

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.

Answer 3

You can try next:

UPDATE student s, playsin p SET s.points = s.points + CONSTANT * p.score WHERE p.name=s.name