Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group