[SQL] Using UPDATE FROM

[Collin Peters]

I have a sql query which hits 5 database tables.  I'll cut a bit out of 
the results and just show the important values

reps_goal       reps_actual     repsvalue
10              10              1
33              5               1
10              12              1
10              12              1
10              10              1
11              11              1
What I'm trying to do is a single UPDATE statement which will take the 
values from the reps_actual column, and put them into the repsvalue 
column.  These two columns come from different tables or obviously you 
could to a simple regular UPDATE.

Here is the UPDATE statement:
UPDATE programactivitysets SET repsvalue = reps_actual
FROM workouts w, workoutactivities wa, workoutactivitysets was, 
programactivities pa, programactivitysets pas
WHERE wa.workout_id = w.workout_id
AND was.workoutactivity_id = wa.workoutactivity_id
AND pa.programactivity_id = wa.programactivity_id
AND pas.programactivity_id = pa.programactivity_id
AND pas.set = was.set
AND w.workout_id = 6036;

After I run that and do a select, these are the results:
reps_goal       reps_actual     repsvalue
10              10              5
33              5               5
10              12              5
10              12              5
10              10              5
11              11              5
For some reason, repsvalue is assigned 5 which is the reps_actual value 
for the 2nd row.  This isn't right.  Am I doing something incorrectly or 
can postgres not handle this kind of UPDATE?

Regards,
Collin Peters
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly