Update with join ignores where clause - updates all rows

From: "Brian" <bengelha(at)comcast(dot)net>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Update with join ignores where clause - updates all rows
Date: 2004-08-28 21:35:46
Message-ID: 000801c48d46$fc84b840$6a00a8c0@cygnusx1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

This WHERE clause finds a single row that has the same year,month,day,hour in another table.
It correctly counts 1 row.

SELECT 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

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

Is the join supported for UPDATE ?

If its supported then I can send the create statements etc so you can reproduce.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2004-08-28 22:03:37 Re: BUG #1224: Restarting postgres appends extra -D argument
Previous Message Tom Lane 2004-08-28 21:23:51 Re: BUG #1236: still in use tablespaces can be removed