From: Collin Peters <cpeters@mcrt.ca>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
Subject: Using UPDATE FROM
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 44
Message-ID: <xx20d.117588$A8.5766@edtnps89>
Date: Thu, 09 Sep 2004 19:54:05 GMT
To: pgsql-sql@postgresql.org

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