When you need to find the row you want to update with a join statement, you can use join statement as same as select statements in PostgreSQL.

TL;DR: UPDATE orders AS v SET price = s.price_per_product FROM products AS s WHERE v.product_id = s.id;

In update statements you can use from and join statement as same as select statements.

Use case for Update with Join

Asynchronous tasks are the best use case for that kind of approach.

A software might insert orders to a database, in background, an asynchronous tool can update their status, costs etc.