To: Collin Peters <cpeters@mcrt.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using UPDATE FROM 
In-reply-to: <xx20d.117588$A8.5766@edtnps89> 
References: <xx20d.117588$A8.5766@edtnps89>
Comments: In-reply-to Collin Peters <cpeters@mcrt.ca>
	message dated "Thu, 09 Sep 2004 19:54:05 +0000"
Date: Sat, 11 Sep 2004 02:17:03 -0400
Message-ID: <7574.1094883423@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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