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
