Re: Update from a table.

Andrew Bell <acbell(at)iastate(dot)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