From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Brian" <bengelha(at)comcast(dot)net> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Update with join ignores where clause - updates all rows |
Date: | 2004-08-29 16:11:28 |
Message-ID: | 26918.1093795888@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"Brian" <bengelha(at)comcast(dot)net> writes:
> The same join here, updates every row in the table which is incorrect.
> update
> dw.prints_by_hour
> set
> count = h.count + w.count
> from
> dw.prints_by_hour_work w , dw.prints_by_hour h
> WHERE
> w.year = h.year
> and w.month = h.month
> and w.day = h.day
> and w.hour = h.hour
No, it's not a bug: it's a self-join. If we identified the target table
with the "h" table then it would be impossible to do self-joins in
UPDATE.
You need to write
update
dw.prints_by_hour
set
count = dw.prints_by_hour.count + w.count
from
dw.prints_by_hour_work w
WHERE
w.year = dw.prints_by_hour.year
and w.month = dw.prints_by_hour.month
and w.day = dw.prints_by_hour.day
and w.hour = dw.prints_by_hour.hour
There's been some talk of allowing an alias to be attached to the target
table ("update dw.prints_by_hour h") which would make it possible to
write the update a bit more compactly, but we haven't done that.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2004-08-30 03:18:54 | Re: [BUGS] server crash in very big transaction [postgresql |
Previous Message | Tom Lane | 2004-08-29 15:07:53 | Re: [BUGS] server crash in very big transaction [postgresql |