PostgreSQL: unexpected update all records in database

Question

I have one prod table and one temp. The task: update all records in prod table with information from temp.

update prod set status = 'on'
  from prod pd
  join temp tm using (factory_id) 
where pd.status = 'off'

unfortunately, that code went to all records in prod table set to 'on', no matter if they are or not in temp.

can you explain how that happened? I thought that only records with status 'off' must be effected. And what should be corrected in the request to make it right?

Answer 1

This happens because of the from, you should mention prod just once:

update prod set status = 'on'
  from temp tm 
where prod.status = 'off'
and prod.factory_id = tm.factory_id;