UPDATE with FROM

UPDATE can include an optional FROM clause, which permits joins to other tables. The FROM clause also allows the use of columns from other tables in the SET clause. With this capability, columns can be updated with data from other tables.

Suppose we want to update the salesorder table's order_date column. Some orders have order_dates earlier than the hire_date of the employee who recorded the sale. For these rows, we wish to set the order_date equal to the employee's hire_date. Figure  shows this query.  

        UPDATE salesorder
        SET    order_date = employee.hire_date
        FROM   employee
        WHERE  salesorder.employee_id = employee.employee_id AND
               salesorder.order_date < employee.hire_date;   

The FROM clause allows the use of the employee table in the WHERE and SET clauses. While UPDATE can use subqueries to control which rows are updated, the FROM clause allows you to include columns from other tables in the SET clause.

Actually, the FROM clause is not even required. The UPDATE in Figure  will work in the same way without its FROM clause. POSTGRESQL automatically creates a reference to any table used in a query. That is, the query SELECT salesorder.* automatically adds salesorder to the FROM clause and executes the query.  Likewise, the query DELETE FROM salesorder WHERE salesorder.order_date = employee.hire_date AND employee.employee_id = 24 uses the employee table. This feature is particularly useful with DELETE because it does not support a FROM clause as SELECT and UPDATE do.