Re: Using UPDATE FROM

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Collin Peters <cpeters(at)mcrt(dot)ca>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Using UPDATE FROM
Date: 2004-09-11 06:17:03
Message-ID: 7574.1094883423@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Collin Peters <cpeters(at)mcrt(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Michalis Kabrianis 2004-09-11 06:22:36 Re: [SQL] PL/pgSQL Function Problem
Previous Message Tom Lane 2004-09-11 06:03:30 Re: PL/pgSQL Function Problem