From: Collin Peters <cpeters@mcrt.ca>
To: pgsql-sql@postgresql.org
Subject: Using UPDATE FROM
Date: Thu, 09 Sep 2004 19:54:05 GMT
Message-ID: <xx20d.117588$A8.5766@edtnps89> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cpeters@mcrt.ca
Subject: Re: Using UPDATE FROM
In-Reply-To: <xx20d.117588$A8.5766@edtnps89>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox