Re: Update from a table.

Andrew Bell <acbell@iastate.edu> writes:
> I want to update table1.netSalary to be table1.salary - table2.deductions where
>    table1.employee = table2.employee.

> I don't see any way to do something like this with the syntax.

You just do it:

UPDATE table1 SET netSalary = table1.salary - table2.deductions
    WHERE table1.employee = table2.employee;

If you want to be slightly clearer you can do

UPDATE table1 SET netSalary = table1.salary - table2.deductions
    FROM table2
    WHERE table1.employee = table2.employee;

so that it's obvious there's a join going on.  But the first will give
you an implicit "FROM table2" anyway.

AFAICT neither of these is legal per SQL92, but I think it's a common
extension.  If you wanted to be pure spec-conformant you'd have to write
something like

UPDATE table1 SET
    netSalary = salary - (SELECT deductions FROM table2
                          WHERE table1.employee = table2.employee);

but this is not any more readable IMHO, and it'll likely be slower
(at least in Postgres, which isn't super smart about rewriting
sub-selects as joins).

            regards, tom lane