pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Using UPDATE FROM 2+ messages / 2 participants [nested] [flat]
* Using UPDATE FROM @ 2004-09-09 19:54 Collin Peters <cpeters@mcrt.ca> 0 siblings, 1 reply; 2+ messages in thread From: Collin Peters @ 2004-09-09 19:54 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using UPDATE FROM @ 2004-09-11 06:17 Tom Lane <tgl@sss.pgh.pa.us> parent: Collin Peters <cpeters@mcrt.ca> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2004-09-11 06:17 UTC (permalink / raw) To: Collin Peters <cpeters@mcrt.ca>; +Cc: pgsql-sql Collin Peters <cpeters@mcrt.ca> writes: > 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; I think you're imagining that "programactivitysets pas" in the FROM clause is the same as the target table "programactivitysets". It's not, at least not in Postgres, though I've heard tell that MS SQL interprets it that way for some unfathomable reason. Drop the FROM entry and s/pas/programactivitysets/ in the WHERE clause, and I think you'll be good to go ... regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-09-11 06:17 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-09-09 19:54 Using UPDATE FROM Collin Peters <cpeters@mcrt.ca> 2004-09-11 06:17 ` Tom Lane <tgl@sss.pgh.pa.us>
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