Update with join ignores where clause - updates all rows
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.
"Brian" <bengelha@comcast.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