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
