Thread: 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  =3D h.year
       and  w.month =3D h.month
       and  w.day   =3D h.day
       and  w.hour  =3D h.hour


The same join here, updates every row in the table which is incorrect.


update
       dw.prints_by_hour
set
       count =3D h.count + w.count
from
       dw.prints_by_hour_work w  , dw.prints_by_hour h
WHERE
              w.year  =3D h.year
       and  w.month =3D h.month
       and  w.day   =3D h.day
       and  w.hour  =3D h.hour

Is the join supported for UPDATE ?

If its supported then I can send the create statements etc so you can repro=
duce.

"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